Creating macro variables from SAS dataset

0
446

Macro variables in SAS are used extensively for reusing codes. You can create a multi-usable program in which the analysis of variables can be controlled by updating the values of macro variables.

LET statement can be used to create a few macro variables which work extremely well. Data step with CALL SYMPUT or INTO statement in PROC SQL provides a better method to automate the creation of macro variables.

However, if you have a large number of variables in a data step that needs to be converted into macro variables, these solutions may require a great deal of typing.

SAS provides an elegant solution to overcome these problems with the CALL SET routine. You can convert data step variables into macro variables with a few lines of code using the %SYSCALL SET routine.

This routine also allows you to convert and process one observation of data step variables at a time thus saving memory usage.

Ways to create Macro Variables in SAS

SAS provides a number of different options for creating macro variables.

1. %LET statement for creating a single macro variable

The %LET statement is one of the easiest methods to create a macro variable and is the equivalent of the DATA step’s assignment statement.

%LET statement is followed by the macro variable name, an equal sign (=), and then the text value to be assigned to the macro variable.

SYNTAX:

%LET  <macro-variable-name> = <macro-variable-value>

Example:

%LET  Name = Smith;

Note that, quotation marks are not used while creating macro variables with the LET statement. Macro variables are neither character nor numeric, they always just stored as a plain text.

When the macro variables &name is created with the %let statement, the value of ‘Smith’, you can use this macro variable across anywhere in your program.

However, if you have more than a few macro variables to create, typing the %let statements can be tedious and error-prone.

2. CALL SYMPUT – To create many macro variables

If the variables that you need to store in macro variables are contained in a SAS dataset, then %LET statements cannot be used to assign those values into a macro variable.

In the below, example the Fname macro variable is supposed to contain the values of the Name variable from the SASHELP.CLASS dataset.

data new_class;
set sashelp.class;
%let FName = Name;
run;

Since %LET statements are executed before the data step is even compiled, the macro variable &name will resolve to ‘Name’ as the value and not the actual value in the data set.

To overcome this problem we will need to use the data step to place information onto the macro variable symbol tables.

We can do this with SYMPUT which is a DATA step call routine, and not a macro language statement.

SYNTAX

CALL SYMPUT(macro-variable, value);

EXAMPLE

data new_class;
set sashelp.class;
call symput('FName',Name);
run;

In the above example, the CALL SYMPUT statement creates the macro variable &Fname containing the variable name. However, only the last value of the variable name is assigned to the macro variable.

To place all of the values of the name into a macro variable, we can include a suffix as in the below example.

data new_class;
set sashelp.class;
suffix=put(_n_,5.);
call symput(cats('FName',suffix),Name);
run;

The suffix variable counts the observation(_n_) and appends count onto the macro variable Fname so that unique Macro variables are created for each name.

The resultant macro variables &Fname1 will store the first name value, the second value is stored in the macro variable &Fname2 and so on.

Now, in order to retrieve any value of the Name variable, you can use the below statement and replace the number with the observation number.

The statement below resolves to ‘Carol‘ which is the 4th Observation in the dataset.

%put &Fname4;

The values are assigned to the macro variable through SYMPUT during DATA step execution so, you cannot create a macro variable using SYMPUT and access that variable in the same data step.

Macro variable references involving the use of an & are resolved before the compilation phase of the DATA step.

Since the DATA step execution phase comes after the entire DATA step is completed, the macro facility would be attempting to resolve a macro variable that would not have been defined until the DATA step’s execution phase is complete.

CALL SYMPUT vs CALL SYMPUTX

According to SAS documentation, the DATA step will use BEST12. to convert the numeric value to a character value.

If you use a numeric variable with an operator that requires a character value, such as the concatenation operator, the numeric value is converted to a character using the BEST12. format.

Because SAS stores the results of the conversion beginning with the right-most byte, you must store the converted values in a variable of sufficient length to accommodate the BEST12. format. You can use the LEFT function to left-justify a result.

Both SYMPUT and SYMPUTX convert the value to a character before assigning it to a macro variable.

  • CALL SYMPUT gives you a message on the log about the conversion, while SYMPUTX does not.
  • CALL SYMPUTX removes any leading and trailing blanks that were caused by the conversion.

3. PROC SQL

You can automate the creation of a macro variable using PROC SQL.

Placing a single value in a macro variable

There are situations where you may require to count the number of observations in a table. The below code is an example of how you can use PROC SQL to count the number of observations and store that count in a macro variable.

proc sql noprint;
	select count(*) into :nobs from sashelp.class;
quit;

The INTO clause is used to create the new macro variable NOBS.

The colon(:) informs the SELECT statement that the result of the COUNT function is to be written into a macro variable.

We can also create macro variables for each of the values as we have created in the data step earlier.

proc sql noprint;
	select count(*) into :nobs from sashelp.class;
	select Name into :Name1-:Name%left(&nobs) from sashelp.class;
quit;

Unlike, the data step method, you must know there are 19 suffixes for the macro Name or you could first calculate the number of Observations and store in a separate macro variable.

In the above example, COUNT function is used to determine the number of observation which are stored into the macro &nobs.

Creating lists of values

It is also possible to create more than one macro variable in the SELECT statement.

In the example below two macro variables are created (&name and &age) each contains a comma-separated list of values.

proc sql outobs=5 noprint;
	select name, age into :names separated by ',', :age separated by '-' from 
		sashelp.class;
quit;

%put names are &names;
%put ages are &age;
%put number of obs &sqlobs;

The OUTOBS=5 option limits the number of observations for processing to 5.

The values of the variables Name and Age will be written INTO macro variables.

The list of values of Name is written into &names with the values separated by a comma. Without theSEPARATED BY clause the individual values will replace previous values rather than be appended onto the list. The last comma is needed to separate the two macro variables (: Names and: age).

The list of the age will be written to &age.

&SQLOBS is an automated macro variable and contains the number number of rows that are processed to create the macro variable list or range. In this case, it contains the number of rows as 5.

73 %put names are &names;
   names are Alfred,Alice,Barbara,Carol,Henry
74 %put ages are &age;
   ages are 14,13,13,14,14
75 %put number of obs &sqlobs;
   number of obs 5

Creating many macro variables using do loop and array

There are situations where you need to convert all of the variables of the dataset into a macro variable.

data _null_;
	set sashelp.class;
	suffix=put(_n_, 5.);
	array num _NUMERIC_;

	do i=1 to dim(num);
		call symputx(cats(vname(num[i]), suffix), num[i]);
	end;
	array char _CHARACTER_;

	do i=1 to dim(char);
		call symputx(cats(vname(char[i]), suffix), char[i]);
	end;
run;

%put &name2;
%put &age2;

As in the above example, we are appending the suffix created from the automatic variable _n_ onto the variable name supplied by the function VNAME. The SAS system requires separate array statements for characters and numeric.

By using the _NUMERIC_ and _CHARCATER_, we can assign all of the variables in the dataset to these arrays. Then, we can use the “*” in the array definition and the DIM function in the loop to the number of variables.

The data step will convert all data set variables to macro variables with the same name and observation suffix.

However, if the dataset has hundreds of observations and dozens of variables, a lot of system resources may be used up in storing the macro variables.

4. CALL SET

You can use the CALL SET statement to replace the SET statement in a SAS data step.

%macro createMacro();
	%let dsid=%sysfunc(open(sashelp.class));
	%let nobs=%sysfunc(attrn(&dsid, nobs));
	%syscall set(dsid);

	%do i=1 %to &nobs;
		%let rc=%sysfunc(fetchobs(&dsid, &i));
		%put &name &age &weight;
	%end;
	%let dsid=%sysfunc(close(&dsid));
%mend;
%createMacro();

The %SYSCALL SET” is the macro version of “CALL SET” which creates a macro variable corresponding to each of the variables in the data set.

When an observation using FETCHOBS, each of these macro variables is populated with the value of the corresponding data set variable.

Now, you have all the dataset variables replaced with the macro variables without any need for PROC SQL or CALL SYMPUT.

Additionally, the macro variables need no suffix which means you do not need a macro variable for each observation. This can significantly reduce the number of system resources.

Conclusion

Four techniques for creating macro variables have been discussed. The choice of creating macro variables depends upon the programmer preferences. For creating a single macro variable, %LET is most adequate. if you want to create many macro variables you can use CALL SYMPUT or PROC SQL.

However, if the requirement is to create many macro variables to process each observation in the data step then the use of CALL SET will lead to concise and efficient coding.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.