Multiple PROC SQLs using Macros

[PraveenBala]

Hi,

I am trying to create multiple data sets, from multiple data sets, using PROC SQL.

All I need is the macro to run several times replacing a set of characters in the data set names in each loop.

The below is NOT working.

Could you please help.

Rgds

Praveen

%macro CITYFCSTS;

%let LIST = NEWYORK LONDON;

%do i=1 %to %sysfunc(countw(&LIST));

%let CITY=%scan(&LIST,&i,%str( ));

proc sql;

create table WORK.&CITY_JOIN as

select unique

a.*, a7.FWDPT_7D, a1m.FWDPT_1M, a3m.FWDPT_3M, a12m.FWDPT_12M

from

SVPRM15.&CITY_SPOT a inner join SVPRM15.&CITY_7D a7

  on a.SNAPDATE = a7.SNAPDATE

  inner join SVPRM15.&CITY_1M a1m

  on a.SNAPDATE = a1m.SNAPDATE

  inner join SVPRM15.&CITY_3M a3m

  on a.SNAPDATE = a3m.SNAPDATE

  inner join SVPRM15.&CITY_12M a12m

  on a.SNAPDATE = a12m.SNAPDATE;

quit;

%end;

%mend;

%CITYF

[RW9]

Hi,

You do not mention what is not working, or provide anything we can work on?

I would do:

data _null_;

     do i="NEWYORK","LONDON";

          call execute('proc sql;

                          create table WORK.'||strip(i)||'_JOIN as

                          select unique

                          a.*, a7.FWDPT_7D, a1m.FWDPT_1M, a3m.FWDPT_3M, a12m.FWDPT_12M

                          from

                          SVPRM15.'||strip(i)||'_SPOT a inner join SVPRM15.'||strip(i)||'_7D a7

                            on a.SNAPDATE = a7.SNAPDATE

                            inner join SVPRM15.'||strip(i)||'_1M a1m

                            on a.SNAPDATE = a1m.SNAPDATE

                            inner join SVPRM15.'||strip(i)||'_3M a3m

                            on a.SNAPDATE = a3m.SNAPDATE

                            inner join SVPRM15.'||strip(i)||'_12M a12m

                            on a.SNAPDATE = a12m.SNAPDATE;

                        quit;');

     end;

run;

[PraveenBala]

Thanks for the response.

Your technique worked. No need of a Macro.

Appreciate the prompt response.

Rgds

Praveen

[Ksharp]

Add a dot after  every &CITY :

create table WORK.&CITY._JOIN as

[RW9]

Agree with you that that's best practice, but it shouldn't change the operation.  I would say that the &CITY. is resolving to something other than expected, i.e.

create table WORK.LONDON  _JOIN as /* Note the two extra spaces */

Use options mlogic mprint symbolgen; to get description or resolution.

However I really don't like macro variables containing lists of things so wasn't going to go there

[Ksharp]

Yeah. I agree with you. But you need to write some macro code to sharp your macro skill. Don't you think so ?

[RW9]

Well, macro language is just a code generator at the end of the day.  You can achieve exactly the same thing with cntrl + c and cntrl + v changing any values.  From what I see, a lot of the use for macros is when the problem is not thought about, i.e. would transposing or normalising make the coding easier, what about arrays, using metadata etc.  I used to use macros all the time, but stepping back and thinking about the data, and how metadata can be stored (e.g. by merging parameters from other datasets) really reduces the need for it. 

[Char]

I think this will work too...

%macro CITYFCSTS(CITY);

proc sql;
create table &CITY_JOIN as
select unique
a.*, a7.FWDPT_7D, a1m.FWDPT_1M, a3m.FWDPT_3M, a12m.FWDPT_12M
from
SVPRM15.&CITY_SPOT a inner join SVPRM15.&CITY_7D a7
  on a.SNAPDATE = a7.SNAPDATE  inner join SVPRM15.&CITY_1M a1m
  on a.SNAPDATE = a1m.SNAPDATE inner join SVPRM15.&CITY_3M a3m
  on a.SNAPDATE = a3m.SNAPDATE inner join SVPRM15.&CITY_12M a12m
  on a.SNAPDATE = a12m.SNAPDATE;
quit;

%mend CITYFCSTS;

%CITYFCSTS(NEWYORK)
%CITYFCSTS(LONDON)