Problem using PROC SQL within a Macro

[GA_Mike]

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,

[ballardw]

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.

[GA_Mike]

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.

[GA_Mike]

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;

[Reeza]

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.;

[Astounding]

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.