DATA Step, Macro, Functions and more

Multiple PROC SQLs using Macros

Reply
Occasional Contributor
Posts: 18

Multiple PROC SQLs using Macros

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

Super User
Super User
Posts: 7,973

Re: Multiple PROC SQLs using Macros

Posted in reply to PraveenBala

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;

Occasional Contributor
Posts: 18

Re: Multiple PROC SQLs using Macros

Thanks for the response.

Your technique worked. No need of a Macro.

Appreciate the prompt response.

Rgds

Praveen

Super User
Posts: 10,035

Re: Multiple PROC SQLs using Macros

Posted in reply to PraveenBala

Add a dot after  every &CITY :

create table WORK.&CITY._JOIN as

Super User
Super User
Posts: 7,973

Re: Multiple PROC SQLs using Macros

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 Smiley Happy

Super User
Posts: 10,035

Re: Multiple PROC SQLs using Macros

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

Super User
Super User
Posts: 7,973

Re: Multiple PROC SQLs using Macros

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. 

Occasional Contributor
Posts: 12

Re: Multiple PROC SQLs using Macros

Posted in reply to PraveenBala

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)

Ask a Question
Discussion stats
  • 7 replies
  • 346 views
  • 1 like
  • 4 in conversation