Macro Quoting Functions & %NRQUOTE

Category="BASE SAS","ADVANCE SAS"

Consider we have a macro named Category and is used in a macro call.

SAS will throw error if this macro variable gets resolved during macro call, as the value of this macro variable contains quotation marks. But we don’t want this macro variable to get resolved during the macro call instead we wanted this value as an observation within a dataset (which is created within the macro).

Hence we can make use of macro quoting function which mask the value of a macro variable helping the macro facility to interpret double quotation marks as text rather than macro language symbols.

With functions like %STR, %NRSTR, %QUOTE, and %NRQUOTE, unmatched quotation marks and parentheses must be marked with a % sign. Where as you don't have to mark unmatched symbols in the arguments of %BQUOTE, %NRBQUOTE, and %SUPERQ.

More: http://www.okstate.edu/sas/v8/sashtml/macro/zenid-37.htm

Send email from SAS

This blog gives a simple example on "how to send email from SAS"

http://www.afhood.com/blog/?p=400

SUM function to ensure that the result is not missing

Use SUM function to ensure that the result is not a missing value.

sum (aaa, bbb);
sum(aaa, -bbb);

Export & Import for CSV Files

***Export a SAS Dataset to CSV File;

%macro test;

DATA x; x=today(); FORMAT x MMDDYYN8.; RUN;
PROC SQL NOPRINT; SELECT x INTO :x FROM x; QUIT; %PUT &x;
OPTIONS missing="";

DATA _NULL_;
SET AAA.sales END=last;

dateheader=compress("H""&x");
FILE "/abc/dev/xyz/sales.csv" DLM=',';
IF _N_=1 THEN PUT dateheader;

var=compress(fcst_acct_yr_i','fcst_acct_mo_i','fcst_acct_mo_wk_i','str_i','sales);
PUT var;

IF last THEN DO;
e=compress("T"_N_);
PUT e;
END;

RUN;
%mend test;
%test;

***Import a CSV File to SAS Dataset;

%let IN_file=/abc/dev/xyz;
%macro test;

PROC IMPORT OUT=WORK.Temp DATAFILE="&in_file.dummy.csv"
DBMS=CSV REPLACE; GETNAMES=YES;
RUN;

%mend test;
%test;

Unlock the dataset

While running SAS jobs concurrently or when a different user had opened the dataset that you need, the following error message appears:

ERROR: A lock is not available for XYZ.abc.
ERROR: Lock held by process 34013400.
NOTE: The SAS System stopped processing this step because of errors.

In this case, copy the process id (34013400) from log and use the below UNIX command to find out which user had locked your dataset and request them to Close it/ Unlock it.

ps -ef I grep "process_id"

Keep required datasets in WORK Library & Kill the rest

The PROC DATASETS procedure provides the SAVE statement which will delete all members in a library except for the ones noted on the SAVE statement:

proc datasets library = work;
save DUMMY;
quit;
run;

Simple BAR Chart

/* format the values for barchart */
proc format;
value grpc (multilabel)
-11 = '< -10%'
-10 = '-10% to -8%'
-8 = '<-8% to -6%'
-6 = '<-6% to -4%'
-4 = '<-4% to -2%'
-2 = '<-2% to 0%'
0 = '<0% to 2%'
2 = '<2% to 4%'
4 = '<4% to 6%'
6 = '<6% to 8%'
8 = '<8% to 10%'
10 = '> 10%'
;
run;

%macro graph;
*path in which the report should be placed;
ods html path = "abc/dev/report"
body = "Dummy_Chart.html"; *report name;

GOPTIONS xpixels=900;
pattern1 color=Green;
axis1 label=(a=90 'Count of Stores ');
axis2 label=('TY XYZ Over LY %')
value=(angle=90);
title1 'TY XYZ Bar Chart';

proc gchart data=work.TY_XYZ_Over_LY;
vbar temp / sumvar=store_count discrete noframe
width=3
raxis=axis1 maxis=axis2
autoref clipref cref=graybb
coutline=Yellow woutline=2;
format temp grpc.;
run;

ods html close;
%mend graph;
%graph;

Invoke external SAS macros in a SAS program

Autocall macro and %include are two common methods to invoke external SAS macros in a SAS program.

Method I: Autocall macro

%let path=/abc/dev/code/xyz;

option mlogic mprint symbolgen merror source source2 mrecall mautosource sasautos=(
"&path"
"!SASROOT/sasautos");

*macro call of SAS program;
%EXAMPLE;

Method II: %include

%include "/abc/dev/code/xyz/EXAMPLE.sas";

Details of related System Options used:

MERROR - issues the following warning message when the macro processor cannot match a macro reference to a compiled macro:

WARNING: Apparent invocation of macro %text not resolved.

SOURCE - specifies to write SAS source statements to the SAS log.

SOURCE2 - specifies to write to the SAS log secondary source statements from files that have been included by %INCLUDE statements.

MRECALL - searches the autocall libraries for an undefined macro name each time an attempt is made to invoke the macro. It is inefficient to search the autocall libraries repeatedly for an undefined macro. Generally, this option is used while developing or debugging programs that call autocall macros.

!SASROOT:

The !SASROOT directory contains the files required to use SAS. This directory includes invocation points, configuration files, sample programs, catalogs, data sets, and executable files.

More: http://support.sas.com/documentation/

UPDATEMODE=

MISSINGCHECK
prevents missing values in transaction data set from replacing values in master data set.

NOMISSINGCHECK
allows missing values in transaction data set to replace values in master data set.

/*create or update permanent dataset based on their exsistence*/
%macro update;
proc sort data=trans; by store; run;

%if %sysfunc(exist(ABC.master))=0 %then %do;
data ABC.master;
set trans;
run;
%end;

%else %do;
data ABC.master;
update ABC.master trans UPDATEMODE=NOMISSINGCHECK;
by store;
run;
%end;

%mend;
%update;

if _n_=1 then set

If a user wants to add single obeservation of one dataset to each record of the other dataset, then the user can make use of _n_=1.

%let Final_Year=2012;
%let Final_Week=4;

/*get the last date from date dataset based on input parameters (to calculate the age of a store)*/
data lday(rename=(wk_end_d=lday));
set acct_date(where=(yr_i=&Final_Year and wk_i=&Final_Week));
run;

/*add the last date to each record of store to calculate the age*/
data store_lday;
if _n_=1 then set lday(keep=lday);
set store;
***** age *****;
if open_date ne . then do;
age_in_wks=intck('week',open_date,lday);
age_in_mth=intck('month',open_date,lday) - (day(lday) < day(open_date));
end;
run;

Simple HTML Report

*/path in which the report should be placed/report name;

%let body=/abc/dev/report/new_stores;

ods html body="&body..html";
title1 "New Stores";
proc report data=new_stores;
column storeid acct_yr_i acct_mo_i dummy_var;

define storeid /display 'Store ID';
define acct_yr_i /display 'Accounting Year';
define acct_mo_i /display 'Accounting Month';
define dummy_var /display 'Dummy Variable';
run;
ods html close;

How user can select values from a dynamic list

In SAS 9.1, the GUI prompts were not able to read the values inside a dataset dynamically. For example, let us consider a dataset named Unique Hour (UNQ_HR) which contains a variable named Unique Category Description (unq_catg_desc). Incase if Unique Category Description of this dataset gets updated every month and if it is used in GUI Prompts, then for every other month the SAS User has to follow the static "uploading of values" in GUI Prompt from the updated datasets (so that the GUI reflects updated values of dataset).

But SAS 9.2 gives a solution/ flexibility for dynamically reading updated values present inside a dataset.

SAS EG 9.1: dynamic UI cannot be created, below is the way in which a static list can be updated

1. Open the XYZ.egp

2. Click on Tools on the top right corner of the SAS EG and select Parameters Manager.

3. The Parameters Manager Window opens up showing the lists of GUI Parameters. Select "unq_catg_desc" and click on the Edit button.

4. The Edit button opens up the Edit Parameter Definition Window. Click on Data Type and Values tab towards the right.

5. The Load Values button is located at the bottom left corner. Click on it to select the SAS Server option.

6. Click on the Servers Icon (located towards the left).

7. Navigate to the path: Servers - SAS Main - Libraries - ABC

8. Locate the UNQ_HR Dataset in ABC Library and click Open button.

9. The column names of the dataset are displayed in the Select Column Window. Select the unq_catg_desc Column Name and click OK.

10. Click on Save and Close.

11. Click on Yes when the Parameters Manager Window asks.

12. Click on Close button to proceed with execution.

SAS EG 9.2: dynamic UI can be created

Pre requisite to create a dynamic UI –

We need to have the respective table in any of the path of Data source in Edit Prompt. If it is not there, then we have to register the required table in required path through SAS DI Studio.

Steps to create dynamic UI –

Step 1: Fill below param’s highlighted in red
Method for populating promt - User selects values from a dynamic list
Number of values - Multiple ordered values
Data source - The path (the path in which the dynamically readable dataset and it value is present)

Step 2: In Data source tab locate the physical path of where that table is (in our case it is ABC.unq_hr)

Step 3: Point to the table and column. Say o.k

User Defined Error & Messages to SAS log

/*throw error when excel input contains duplicate values*/
proc sort data = excel_read_in out = dedup
dupout = dups nodupkey;
by str_i fcst_acct_yr_i fcst_acct_mo_i fcst_acct_mo_wk_i;
run;

proc sql noprint;
select count (*) into : nobs_err1 from dups;
run;
%put &nobs_err1;

DATA _NULL_;
%IF &nobs_err1 > 0 %THEN %DO;
PUT "ERROR: The excel sheet is loaded with duplicate values. Please have a look at rmtwork.dups dataset and eliminate duplicate values in excel sheet.";
ABORT;
%END;
RUN;

Inactive Foreign Key Error (Migration issues of SAS 9.1 to 9.2)

Foreign key integrity constraints can become inactive when SAS data sets are moved via the COPY, CPORT, CIMPORT, UPLOAD, or DOWNLOAD procedures. It is possible to happen with PROC MIGRATE too. The reason may be because of PROC MIGRATE being potential enough to migrate datasets with integrity constraints but not with referential constraints. In that case, a user has to activate the foreign key by using IC REACTIVATE.

proc datasets library=XYZ;
modify AAA;
ic reactivate for references ABC;
run;
quit;

Things to do -

1. Execute proc contents of the dataset to view the inactive foreign key.
2. Execute the above proc statement for reactivating the respective foreign key.
3. Once executed check the log which would say that the foreign key is reactivated
4. Now execute proc contents again for the same dataset, to see the status as null for “Inactive” column.

IMPORTANT: The “describe table constraints” has to be executed (in the old box - SAS 9.1) to understand the referential constraints and to pass the dataset to the ‘references’ of ic reactivate statement.


ERROR WHILE EXECUTING IC REACTIVATE

The below error is expected to occur if the foreign key reference dataset (ABC.STR) has lost its constraints during UNIX copying (which should actually be migrated with PROC MIGRATE).

2984 proc datasets library=XYZ;
2985 modify AAA;
2986 ic reactivate forkey references ABC;
ERROR: Primary key does not exist in data set ABC.STR.DATA.
2987 run;

Issues Related To System Options (Migration issues of SAS 9.1 to 9.2)

1. A variable name can be created with space in SAS EG 4.1 without explicitly adding the option VALIDVARNAME ANY. Whereas the same couldn't be done in 4.3.

2. In the SAS EG 4.1 the user had write access to the SASUSER library. Whereas in 4.3, the write access has been denied.

Both the above issues were related to SAS SYSTEM OPTIONS differing between 4.1 and 4.3 (in our ENV). Below are the details of difference:

SAS Version 4.1: Option Name - Settings Description/ its use

RSASUSER NORSASUSER - Enables a user to open a file in the Sasuser library for update. VALIDVARNAME ANY - A variable name with space can be created.

SAS Version 4.3: Option Name - Settings Description/ its use

RSASUSER RSASUSER - Limits access to the Sasuser data library to read-only.
VALIDVARNAME V7 - Control the type of SAS variable names that can be created during a SAS session.

Requesting for change in System Options will resolve this issue.

Why Proc Migrate (Migration issues of SAS 9.1 to 9.2)

“PROC_MIGRATE” - Migrates datasets with its integrity constraints. Whereas a simple FTP through UNIX will make the dataset lose its integrity constraints.

Enclose Values Within Quotes (Migration issues of SAS 9.1 to 9.2)

In old box (i.e., SAS 9.1), there was an option "Enclose values within quotes" in GUI Prompts that ensured all values in the list that the code substituted during execution was within quotes as they were string.

Example: %LET unq_catg_desc = %STR("Bakery");

In new box (i.e., SAS 9.2), this option is not available and the code error out as it is specifically looking for string. Hence the user will have to add it in the code.

Example: %LET unq_catg_desc = Bakery;

Migrating SAS Enterprise Guide Projects (Migration issues of SAS 9.1 to 9.2)

When migrating an Enterprise Guide project from SAS 9.1 (EG 4.1) to SAS 9.2 (EG 4.3) there are three main techniques that are available to achieve this.

Those are:

1. Opening the EG 4.1 project in EG 4.3 and save. This approach is suitable if there are no required changes to Server names, Libnames, paths etc.

2. Utilizing the Enterprise Guide Migration Wizard (in Citrix Folder). This approach is used for migrating multiple EG Projects at one shot.

3. Manual Migration with Project Maintenance wizard via Tools. The Enterprise Guide 4.1 project needs to be opened in Enterprise Guide 4.3 so as to manually make the necessary changes.

Refer the below link for more details on each option:
http://support.sas.com/resources/papers/proceedings11/313-2011.pdf

Prompts Manager Display Extra Decimal Places (Migration issues of SAS 9.1 to 9.2)

In parameter manager of SAS EG 4.1, when a macro variable is created with ‘Float’ as Data Type and when integers are entered in the List of values there is no difference in the appearance of GUI Screen. The GUI Screen displayed integer values only.

Whereas when the same EGP when migrated to the new version 4.3, the prompts manager adds ‘.0’ to all values in the list. Hence the GUI Screen displays all values with ‘.0’. This is because of this feature being set in auto correction mode.

One can make required modification in SAS EG 4.1 and then migrate it. This will resolve the issue.

Using Multiple Selection Prompts (Migration issues of SAS 9.1 to 9.2)

Web Suggestion: (From Angela Hall)

For example in the old box (i.e., SAS 9.1), let us say we create a prompt for region (called 'region_prompt') and then use that in the query of sashelp.shoes. The GUI Prompt created only one macro variable called region_prompt which contained all user selection:

proc sql; create table test as select * from sashelp.shoes where shoes.region="region_prompt";
quit;

But now in the new box (i.e., SAS 9.2), if we allow users to select 1 or more values for region, SAS creates n number of macro variables with the same name but adding _Count to it. Such as region_prompt_count, this represents the amount of selections the user chose. Therefore the SQL query needs to take all of these selections into account. ALSO - if only 1 selection is chosen, there is no region_prompt1 - so you must account for that as well. Here is an example:

proc sql; create table test as select * from sashelp.shoes
where shoes.region in (
%if REGION_PROMPT_COUNT = 1 %then "&Region_Prompt";
%else %do i=1 %to &REGION_PROMPT_COUNT;
"&&Region_Prompt&i"
%end;
);
quit;

SAS Tech Support Suggestion:

Since the user is writing their own code that uses the prompts, Enterprise Guide does not automatically change over the macro variable code (like it would if they were using the prompt in a Query). But user can go through and update their code where they had the WHERE clause so that it uses the new %_eg_WhereParam macro variable. This is what is now used to account for parameter lists. This is how it would look in the code. The first parameter is the dataset.variable the user is querying, the second is the macro variable, the third is the operator, and the fourth is S or N for string or numeric type.
where %_eg_WhereParam(a.unq_catg_desc, unq_catg_desc, IN, TYPE=S)

Both these suggestion will work for a single code but user cannot make changes to each and every code during Migration and hence can make use of the below autocall macro:

%macro param_macro(var= /*Required Macro variable Name*/
,FMT_Char=/*Y/N Required to present it with quote or without*/);
/*
Name : param_macro.sas
Purpose : %param_macro(), converts an array of values entered in an
UI prompt to 1 macro variable

Call the macro by passing the macro variable name assigned
in the parameter manager.

Usage : OPTIONS SASAUTOS=('Path' '!SASROOT/sasautos');
%param_macro(var=)
*/

options mlogic mprint symbolgen;

%global &&var.;

Data _null_;
length x $20000.;
%if &&&var._COUNT GT 0 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var.1"'"';
%end;
%else %do;
x="&&&var.1";
%end;
%end;
%if &&&var._COUNT = 1 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var."'"';
%end;
%else %do;
x = "&&&var.";
%end;
%end;
%else %do i=2 %to &&&var._COUNT;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = strip(x)',"'"&&&&&var.&i"'"';
%end;
%else %do;
x = strip(x)",""&&&&&var.&i";
%end;
%end;
call symput("&var.",x);
run;

%put NOTE: Number of selections made in the UI : &&&var._COUNT;
%put NOTE: Macro Variable &&var. resolves to: &&&var.;
%mend param_macro;

More: http://blogs.sas.com/content/bi/2009/11/10/using-multiple-selection-prompts-in-sas-stored-process-code/

Describe View

Below is the piece of SAS code to find view definition –

proc sql;
describe view libname.view;
quit;

Output:
NOTE: SQL view LIBNAME.VIEW is defined as:

select distinct str.STR_ID
from XYZ.AAA str
where str.STR_ID= 101;

Make use of view definition in log and recreate view as below (If migrated to a new version of SAS)–

proc sql;
create view LIBNAME.VIEW as
select distinct str.STR_ID
from XYZ.AAA str
where str.STR_ID= 101;
quit;

Query DICTIONARY Tables and SASHELP Views

To access SAS System Information, user needs to query DICTIONARY Tables and SASHELP Views

proc sql;
create table work.XOPTIONS as
select * from dictionary.OPTIONS;
quit;

proc sql;
create table work.XVIEWS as
select * from dictionary.VIEWS;
quit;

proc sql;
create table work.XTABLE_CONSTRAINTS as
select * from dictionary.TABLE_CONSTRAINTS;
quit;

proc sql;
create table work.XREFERENTIAL_CONSTRAINTS as
select * from dictionary.REFERENTIAL_CONSTRAINTS;
quit;

proc sql;
create table work.XCHECK_CONSTRAINTS as
select * from dictionary.CHECK_CONSTRAINTS;
quit;

proc sql;
create table work.XCONSTRAINT_TABLE_USAGE as
select * from dictionary.CONSTRAINT_TABLE_USAGE;
quit;

proc sql;
create table work.XCONSTRAINT_COLUMN_USAGE as
select * from dictionary.CONSTRAINT_COLUMN_USAGE;
quit;

proc sql;
create table work.XINDEXES as
select * from dictionary.INDEXES;
quit;

proc sql;
create table work.XFORMATS as
select * from dictionary.FORMATS;
quit;

proc sql;
create table work.XLIBNAMES as
select * from dictionary.LIBNAMES;
quit;

proc sql;
create table work.XMACROS as
select * from dictionary.MACROS;
quit;

proc sql;
create table work.XCATALOGS as
select * from dictionary.CATALOGS;
quit;

proc sql;
create table work.ODICTIONARIES as
select * from dictionary.DICTIONARIES;
quit;

proc sql;
create table work.OMEMBERS as
select * from dictionary.MEMBERS;
quit;

proc sql;
create table work.OGOPTIONS as
select * from dictionary.GOPTIONS;
quit;

proc sql;
create table work.OSTYLES as
select * from dictionary.STYLES;
quit;

proc sql;
create table work.OTABLES as
select * from dictionary.TABLES;
quit;

proc sql;
create table work.OTITLES as
select * from dictionary.TITLES;
quit;

DATA work.OVCOLUMN;
SET sashelp.VCOLUMN;
RUN;

DATA work.OVEXTFL;
SET sashelp.VEXTFL;
RUN;