Hello All, I have a macro I have written to execute a Proc SQL statement on a number of variables. I am fairly new to the macro facility and is likely the cause of the issue I am seeing. In this example there a 9 data sets in the library. There are also variables created from a different macro called ds1, ds2, ds3, ds4, ds5, ds6, ds7, ds8 and ds9 which contain the actual data set name. The macro was written as I the number of data sets in the library can change from one run to the next with completely different data sets. However, as it is currently written, I get the following 2 SAS errors for each of the 9 data sets:
ERROR 22-322: Expecting a name.
ERROR 200-322: The symbol is not recognized and will be ignored.
I know there are other ways in which to get record counts, but note this is just an example to ensure I can process the data sets as the true resulting operations will be a bit more complex (No need to suggest easier methods to get record counts). Any assistance on how I can reference the macro variable associated with the string 'ds' and the iteration variable i to reference the resolved macro variable names ds1 if on iteration 1, ds2 if on iteration 2, etc. I have bolded the lines of code to which my question pertains to. Oh we are on version 7.100.2.3 if that makes a difference.
The macro code is written as:
%macro GetRecordCounts(lib, worklib=work);
data &worklib..DataSetNames;
set &worklib..DataSetNames end=end;
rownum=_n_;
call symputx('ds'||trim(put(rownum,5. -L)),trim(memname));
run;
proc sql;
select max(rownum) Into :dscount From &worklib..DataSetNames;
quit;
%do i = 1 %to &dscount;
proc sql;
Select '&&dsn' as DataSetName, Count(*) As RecordSetCount From &lib..&'ds'||&i.;
Quit;
%end;
%mend;
First of all, single quotes will prevent the resolution of macro variables.
Second, if you need to indirectly resolve macro variables, do this:
%let var2=XX;
%let i=2;
%let result=&&var&i;
%put result=&result;
I also guess that using '&&dsn' is a mistake?
First of all, single quotes will prevent the resolution of macro variables.
Second, if you need to indirectly resolve macro variables, do this:
%let var2=XX;
%let i=2;
%let result=&&var&i;
%put result=&result;
I also guess that using '&&dsn' is a mistake?
Thanks Kurt! That was exactly what I needed. Much appreciated. Also, thanks for pointing out the &&dsn. After adding double quotes and using dsn as the variable name for indirect resolution of the macro variables, it now prints the data set name as intended. Kind regards!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.