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
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;
Thanks for the response.
Your technique worked. No need of a Macro.
Appreciate the prompt response.
Rgds
Praveen
Add a dot after every &CITY :
create table WORK.&CITY._JOIN as
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
Yeah. I agree with you. But you need to write some macro code to sharp your macro skill. Don't you think so ?
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.
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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.