topic Re: Creating a Macroroutine & %LET/DSN statements for Two Data Sets in SAS Studio

Creating a Macroroutine & %LET/DSN statements for Two Data Sets

tpage — Sun, 01 Dec 2019 02:30:28 GMT

Sorry if the title is confusing. I am trying to create a macro routine for two datasets, SASUSER.MOVIES and SASUSER.ACTORS. Below is the code that I've created:

%LET DSN = SASUSER.MOVIES ;
%LET DSN2 = SASUSER.ACTORS
%LET VAR = TITLE ;
%LET CLASS_VAR = Rating ;
%MACRO stats ;
proc means data=&DSN,&DSN2 maxdec=0 ;
var &VAR ;
class &CLASS_VAR ;
run ;
%MEND stats;

%stats ;

Also, when I tried to run it, the log produces 6 error messages:

ERROR: Open code statement recursion detected.

76 %LET CLASS_VAR = Rating ;

ERROR: Open code statement recursion detected.

77 %MACRO stats ;

ERROR: Open code statement recursion detected.

78 proc means data=&DSN,&DSN2 maxdec=0 ;

79 var &VAR ;

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

80 class &CLASS_VAR ;

_____

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: No matching %MACRO statement for this %MEND statement.

81 run ;

82 %MEND stats ;

83 %stats ;

How can I fixed this code so that it can run 2 different DSN's and allow me to work with the TITLE column/variable? Thanks in advance.

Re: Creating a Macroroutine & %LET/DSN statements for Two Data Sets

unison — Sun, 01 Dec 2019 03:01:00 GMT

Just on a quick glance, you need a semicolon here:

%LET DSN2 = SASUSER.ACTORS;

-unison

Re: Creating a Macroroutine & %LET/DSN statements for Two Data Sets

Reeza — Sun, 01 Dec 2019 03:04:54 GMT

You cannot pass two data sets to proc means in this fashion. You need to loop it through the data sets. Or call the macro once for each data set. The macro appendix has examples of loop and the tutorial has examples on calling it multiple times.

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@tpage wrote:

Sorry if the title is confusing. I am trying to create a macro routine for two datasets, SASUSER.MOVIES and SASUSER.ACTORS. Below is the code that I've created:

%LET DSN = SASUSER.MOVIES ;
%LET DSN2 = SASUSER.ACTORS
%LET VAR = TITLE ;
%LET CLASS_VAR = Rating ;
%MACRO stats ;
proc means data=&DSN,&DSN2 maxdec=0 ;
var &VAR ;
class &CLASS_VAR ;
run ;
%MEND stats;

%stats ;

Also, when I tried to run it, the log produces 6 error messages:

ERROR: Open code statement recursion detected.

76 %LET CLASS_VAR = Rating ;

ERROR: Open code statement recursion detected.

77 %MACRO stats ;

ERROR: Open code statement recursion detected.

78 proc means data=&DSN,&DSN2 maxdec=0 ;

79 var &VAR ;

79 var &VAR ;

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

80 class &CLASS_VAR ;

80 class &CLASS_VAR ;

_____

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: No matching %MACRO statement for this %MEND statement.

81 run ;

82 %MEND stats ;

83 %stats ;

How can I fixed this code so that it can run 2 different DSN's and allow me to work with the TITLE column/variable? Thanks in advance.

Re: Creating a Macroroutine & %LET/DSN statements for Two Data Sets

r_behata — Sun, 01 Dec 2019 03:07:04 GMT

Try this Modified Code :

%LET DSN = SASUSER.MOVIES ;
%LET DSN2 = SASUSER.ACTORS;
%LET VAR = TITLE ;
%LET CLASS_VAR = Rating ;

%MACRO stats(dsname);
	proc means data=&dsname maxdec=0;
		var &VAR;
		class &CLASS_VAR;
	run;

%MEND stats;

%stats(&DSN)
%stats(&DSN2)

Issues Identified in your code :

1. Statement not ended with a semi-colon :

%LET DSN2 = SASUSER.ACTORS

The above has resulted in the error :

ERROR: Open code statement recursion detected.

2. Invalid Syntax :

proc means data=&DSN,&DSN2

You cannot pass multiple dataset names to the proc at the same time ,the syntax is invalid. You can parameterize the macro and call it twice , each time with a different dataset name in this fashion :

%MACRO stats(dsname);
....
...
%Mend;

%stats(&DSN) %stats(&DSN2)

Re: Creating a Macroroutine & %LET/DSN statements for Two Data Sets

PaigeMiller — Sun, 01 Dec 2019 13:23:30 GMT

An extremely important concept in writing macros is that they must produce legal valid working SAS code after the macro variables are resolved.

proc means data=&DSN,&DSN2 maxdec=0 ;

resolves to

proc means data=sasuser.movies,sasuser.actors maxdec=0 ;

which is not legal valid working SAS code. You can't have DATA= followed by the names of two SAS data sets, and you can't have DATA= with a comma in it.