BookmarkSubscribeRSS Feed
Chang
Quartz | Level 8

Hi,

I have a working SAS macro that writes part of PROC SQL code, which I use it to vertically stack multiple datasets that have the same data structure as a single dataset. An oversimplified of using the macro as the following:

proc sql;
create table work.test_data as
%PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=BCVAYN, parameter3=%STR(, BCVAYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=CHYPYN, parameter3=%STR(, CHYPYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=CS, parameter1=FCNSALL, parameter2=CSYN, parameter3=%STR(, CSYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=FCS, parameter1=FCRSALL, parameter2=FCSYN, parameter3=%STR(, FCSYNSP as parameter3))	
	order by subjectid, eventname, parameter1;
quit;

I am looking to make the macro more flexible so that it could pass SAS code to the macro definition. I thought I would be able to pass part of my PROC SQL code through the use of the %STR() function, however, I got stuck when I specify a parameter using a previously specified parameter as the following code:

proc sql;
create table work.test_data as
%PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=%STR(&parameter1. as newvar length=200), parameter3=%STR(, BCVAYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=%STR(&parameter1. as newvar length=200), parameter3=%STR(, CHYPYNSP as parameter3))
	order by subjectid, eventname, parameter1;
quit;

This results in a WARNING saying Apparent symbolic reference parameter1 not resolved and the new variable generated by parameter2 has incorrect values. I have tried a number of other functions like %QUOTE, %NRSTR% but none of them work.

 

Although I am unable to provide you with a sample data for testing, the following example maybe able to mimic the usage above

%macro test(v1=,v2=);
  %put &v2.;
%mend;
%test(v1=BCVAALL,v2=%STR(&v1. as anotherVar));

As the following output shows, the macro calling above reproduces the WARNING (line 43) but the value of &v2 seems to be correctly printed (line 44)

Chang_0-1658273719440.png

If it is not appropriate to reference a previously specified parameter in the same macro, my last resort would be to just type up the variable names like

proc sql;
create table work.test_data as
%PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=%STR(BCVAALL as newvar length=200), parameter3=%STR(, BCVAYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=%STR(CHYPALL as newvar length=200), parameter3=%STR(, CHYPYNSP as parameter3))
	order by subjectid, eventname, parameter1;
quit;

Any comments would be highly appreciated.
Chang

 

 

3 REPLIES 3
SASKiwi
PROC Star

What about a simple fix like this?

proc sql;
create table work.test_data as
%PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=%STR(BCVAALL as newvar length=200), parameter3=%STR(, BCVAYNSP as parameter3))
	union all
%PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=%STR(CHYPALL as newvar length=200), parameter3=%STR(, CHYPYNSP as parameter3))
	order by subjectid, eventname, parameter1;
quit;

PARAMETER1 is only usable within the macro itself, not in another parameter being passed into the macro. 

Tom
Super User Tom
Super User

WHY?

Which of these two statements would you rather type, read, maintain?

%mymacro(data=BCVA, parm1=BCVAALL, parm2=BCVAALL as newvar length=200, parm3=%STR(, BCVAYNSP as parameter3))
%mymacro(data=BCVA, parm1=BCVAALL, parm2=%STR(&parm1. as newvar length=200), parm3=%STR(, BCVAYNSP as parameter3))

The first one is SHORTER, CLEARER and it also WORKS.

FreelanceReinh
Jade | Level 19

Hi @Chang,

 

You could avoid the macro processor's attempt to resolve the macro variable reference prematurely by supplying something else in the macro call and then let code in the macro translate that "something" into what you actually need.

 

Simple example: Omit the ampersand in the macro call and insert it in the macro.

%macro test(v1=,v2=);
  %let v2=&&&v2;
  %put &v2.;
%mend;
%test(v1=BCVAALL,v2=v1 as anotherVar);

 

Edit:

Second example:

%macro test(v1=,v2=);
  %let v2=%sysfunc(translate(&v2,&,#));
  %put &v2.;
%mend;
%test(v1=BCVAALL,v2=#v1 as anotherVar);
%test(v1=BCVAALL,v2=XYZ as anotherVar);

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 384 views
  • 2 likes
  • 4 in conversation