topic Re: Multiple PROC SQLs using Macros in SAS Programming

Multiple PROC SQLs using Macros

PraveenBala — Thu, 09 Apr 2015 09:30:12 GMT

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

Re: Multiple PROC SQLs using Macros

RW9 — Thu, 09 Apr 2015 09:46:42 GMT

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;

Re: Multiple PROC SQLs using Macros

PraveenBala — Thu, 09 Apr 2015 10:07:53 GMT

Thanks for the response.

Your technique worked. No need of a Macro.

Appreciate the prompt response.

Rgds

Praveen

Re: Multiple PROC SQLs using Macros

Ksharp — Thu, 09 Apr 2015 13:28:06 GMT

Add a dot after every &CITY :

create table WORK.&CITY._JOIN as

Re: Multiple PROC SQLs using Macros

RW9 — Thu, 09 Apr 2015 13:33:47 GMT

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

Re: Multiple PROC SQLs using Macros

Ksharp — Thu, 09 Apr 2015 13:53:44 GMT

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

Re: Multiple PROC SQLs using Macros

Char — Thu, 09 Apr 2015 14:14:46 GMT

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)

Re: Multiple PROC SQLs using Macros

RW9 — Thu, 09 Apr 2015 14:31:55 GMT

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.