topic Problem using PROC SQL within a Macro in SAS Programming

Problem using PROC SQL within a Macro

GA_Mike — Tue, 10 Jun 2014 14:17:25 GMT

Hello,

My code looks similar to this:

%Macro CFT(PGM,ID);

PROC SQL;

CREATE TABLE work.tempRV AS

SELECT DISTINCT RV

from work.SPECS a

WHERE a.Revision = &ID.;

PROC SQL;

SELECT cats(count(*))

into :MAXRV

FROM work.tempRV;

proc sql;

select DISTINCT RV

into :RV1-:RV&MAXRV.

from work.tempRV;

quit;

%Mend;

When the macro is called, the first PROC SQL runs as expected but the second and third do not execute. Any help would be greatly appreciated.

Thanks,

Re: Problem using PROC SQL within a Macro

ballardw — Tue, 10 Jun 2014 14:27:56 GMT

Do the steps run if you do not use macro code? Does work.tempRV actually have any records?

I do question why use the cats(count(*)) structure as it shouldn't be any different than count(*).

And if the code is similar it isn't identical so we may not be much help.

Re: Problem using PROC SQL within a Macro

Reeza — Tue, 10 Jun 2014 14:46:43 GMT

You have a scope problem, I'm guessing. The macro variables are created in the process but are not available afterwards.

%Macro CFT(ID=);

PROC SQL;

CREATE TABLE work.tempRV AS

SELECT DISTINCT name

from sashelp.class a

WHERE sex = "&ID.";

proc sql;

SELECT cats(count(*))

into :MAXRV

FROM work.tempRV;

proc sql;

select DISTINCT name

into :RV1-:RV&MAXRV.

from work.tempRV;

quit;

%put &rv1.;

%put &maxrv;

%put &&&rv&maxrv.;

%Mend;

%cft(ID=F);

%put &rv1.;

%put &maxrv;

%put &&&rv&maxrv.;

Re: Problem using PROC SQL within a Macro

GA_Mike — Tue, 10 Jun 2014 14:57:45 GMT

They do run if they are outside of the macro.

work.tempRV does contain records.

I can't remember why I switched from count(*) to cats(count(*)). There was something in the data that was causing an issue and that is how I resolved it.

By similar what I mean is that I changed the permanent library names to 'work' just to try to avoid confusion.

Re: Problem using PROC SQL within a Macro

GA_Mike — Tue, 10 Jun 2014 15:27:20 GMT

Ok. I found the reason for cats(count(*)) in my notes. If I only used count(*) in the third PROC SQL the definition of the macro variable RV errors due to leading spaces.

proc sql;

select DISTINCT RV

into :RV1-:RV&MAXRV. <-- So right here instead of resolving to into :RV1-:RV10 it does this into :RV1-:RV 10 and errors.

from work.tempRV;

quit;

Re: Problem using PROC SQL within a Macro

Astounding — Tue, 10 Jun 2014 15:46:36 GMT

Reeza has the right idea here.

PROC SQL runs every time (with complications if the incoming data set happens to be empty). It creates your macro variables. However, it always creates them in the LOCAL symbol table. By the time you try to use them, %CFT has finished executing and its LOCAL symbol table has been erased automatically.