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!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.