topic Re: PROC SQL and MACROS in SAS Programming

PROC SQL and MACROS

gdaymte — Thu, 18 Aug 2016 19:11:51 GMT

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.

Re: PROC SQL and MACROS

LinusH — Thu, 18 Aug 2016 19:34:34 GMT

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.

Re: PROC SQL and MACROS

gdaymte — Thu, 18 Aug 2016 20:54:48 GMT

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.

Re: PROC SQL and MACROS

ballardw — Thu, 18 Aug 2016 21:44:16 GMT

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.

Re: PROC SQL and MACROS

ChrisNZ — Thu, 18 Aug 2016 23:17:56 GMT

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;

Re: PROC SQL and MACROS

BrunoMueller — Fri, 19 Aug 2016 06:43:16 GMT

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

Re: PROC SQL and MACROS

RW9 — Fri, 19 Aug 2016 08:02:08 GMT

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;

Re: PROC SQL and MACROS

gdaymte — Mon, 29 Aug 2016 14:53:22 GMT

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;