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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.