BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gdaymte
Obsidian | Level 7

I'm sure this is something super easy to do, but I am just blanking on how to do this.

 

I have writen several proc sql and data statements that do different things throughout my SAS program. I was running the code on one year to test things out. What I need to do now is run my code for multiple years (2010-2015). So in my code below, wherever you see 2012, that needs to update based on the years of interest. Since it is a lot of code, I wanted to be able to have a macro fill that information in for me.

 

PROC SQL;
CONNECT TO ODBC (DATAsrc='O Prod'); CREATE TABLE O_TEST2012 AS SELECT *
FROM CONNECTION TO ODBC
(EXEC TEST.USP_PATIENT '2012-01-01', '2012-12-31') AS ODBC; DISCONNECT FROM ODBC; QUIT;

DATA M1_2012; SET O_TEST2012;
DO DTHOSPSTAY = DTADMIT TO DTDISCH;
OUTPUT;
END;
FORMAT DTHOSPSTAY MMDDYY10.;
RUN;

 

I am used to using macros in data steps, but have never done so in proc sql.

 

%MACRO YEARS(START,STOP);
	%DO YEAR = &START %TO &STOP;
	%END;

{........CODE...........}

%MEND YEARS;

%YEARS(2010,2015);

 

From what I can see online, macros don't work the same way in proc sql as they do in a data step. Any help would be appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

hi

 

Since you are using SQL Pass-Through, you can not use the double quotes to resolve macro trigger like &name and %name.

 

However the is a simple solution to this, use the %TSLIT autocall macro to resolve macro triggers and put them into single quotes.

 

Here is a simple example:


%MACRO YEARS(START,STOP);
	%DO YEAR = &START %TO &STOP;
    %put NOTE: TEST.USP_PATIENT %tslit(&year.-01-01) , %tslit(&year.-01-01);
	%END;

%MEND YEARS;

%YEARS(2010,2015)

Bruno

View solution in original post

7 REPLIES 7
LinusH
Tourmaline | Level 20

Macro generates SAS code, it doesn't care if it is a data step, SQL or another PROC. SO go ahead and try.

But from your snippet, I would suspect that the dynamic code you wish to generate should be inside the %DO loop, not after.

Data never sleeps
gdaymte
Obsidian | Level 7

I've tried it both ways, but neither seem work. I keep getting a message stating "WARNING: Apparent symbolic reference YEARS not resolved." I went through and checked to maked sure everywhere I called upon &YEARS, that it matched up to the original macro name. It also throws a Syntax Error in my EXEC statement when it calls upon the stored procedure.

ballardw
Super User

Please show the log entry after running the code with OPTIONS MPRINT;

And it may not hurt to show the actual macro code. Debugging code is very difficult without the code.

ChrisNZ
Tourmaline | Level 20

As mentionned, macros just generate text (code in this case).

 

Here is an example of a macro used in SQL:

 

 

proc sql;
  create table CLASS as
  select * 
  from SASHELP.CLASS
  where  %macro loop;
           %do i=10 %to 18 %by 2;
           %if &i>10 %then or ;
           AGE=&i
           %end;
         %mend;
         %loop
   order by NAME;
quit;
BrunoMueller
SAS Super FREQ

hi

 

Since you are using SQL Pass-Through, you can not use the double quotes to resolve macro trigger like &name and %name.

 

However the is a simple solution to this, use the %TSLIT autocall macro to resolve macro triggers and put them into single quotes.

 

Here is a simple example:


%MACRO YEARS(START,STOP);
	%DO YEAR = &START %TO &STOP;
    %put NOTE: TEST.USP_PATIENT %tslit(&year.-01-01) , %tslit(&year.-01-01);
	%END;

%MEND YEARS;

%YEARS(2010,2015)

Bruno

gdaymte
Obsidian | Level 7

This worked for the SQL pass-through part, but I also used dates elsewhere in my code. To account for the dates further down in my where statement, I had to make sure to use double qoutes.

 

PROC SQL;
	CREATE TABLE P_ONE_&YEAR AS
		SELECT *
		FROM O_TEST&YEAR
		WHERE O_EXCLUDE=0 AND V_EXCLUDE=0 AND ("01JAN&YEAR."D <= RX_DTFILLED2 <= "31DEC&YEAR."D)
		GROUP BY PERSONID, RX_QTRFILLED
		ORDER BY PERSONID, RX_DTFILLED2
		;
QUIT;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As an alternative, why not use the Actual Programming language itself:

data years;
  do year=&start. to &stop.;
    output;
  end;
run;

proc sql;
  create table WANT as
  select    ...
  from      ...
  where  YEAR in (select YEAR from YEARS);
quit;

sas-innovate-2024.png

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.

 

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
  • 7 replies
  • 1454 views
  • 4 likes
  • 6 in conversation