DATA Step, Macro, Functions and more

PROC SQL and MACROS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

PROC SQL and MACROS

[ Edited ]

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.

 


Accepted Solutions
Solution
‎08-29-2016 10:45 AM
SAS Super FREQ
Posts: 683

Re: PROC SQL and MACROS

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


All Replies
Super User
Posts: 5,257

Re: PROC SQL and MACROS

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
Occasional Contributor
Posts: 15

Re: PROC SQL and MACROS

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.

Super User
Posts: 10,500

Re: PROC SQL and MACROS

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.

PROC Star
Posts: 1,562

Re: PROC SQL and MACROS

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;
Solution
‎08-29-2016 10:45 AM
SAS Super FREQ
Posts: 683

Re: PROC SQL and MACROS

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

Occasional Contributor
Posts: 15

Re: PROC SQL and MACROS

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;
Super User
Super User
Posts: 7,404

Re: PROC SQL and MACROS

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 392 views
  • 4 likes
  • 6 in conversation