I have the following code:
%macro initial (first=, second=, third=, fourth=, final=);
data &first;
set wtnodup.&first;
DATE1 = INPUT(PUT(Date,8.),YYMMDD8.);
format DATE1 monyy7.;
RUN;
proc freq data=&first order= freq;
tables date1*jobboardid / list out=&second (drop = percent rename=
(Count=CountNew));
run;
data &third;
set &second (firstobs=2);
if countnew le 49 then delete;
run;
proc sort data = &third;
by jobboardid Date1;
run;
data &fourth (keep = countnew oldcountnew Date1 rate from till jobboardid
rate);
set &third;
by jobboardid Date1;
format From Till monyy7.;
from = lag12(Date1);
oldcountnew = lag12(countnew);
if lag12(jobboardid) EQ jobboardid and
INTCK('month', from, Date1) EQ 12 then do;
till = Date1;
rate = ((countnew/oldcountnew)-1)*100;
output;
end;
run;
proc sort data = &fourth;
by Date1 rate;
proc means data=&fourth noprint;
by Date1;
output out=Result.&final median(rate)=medianRate;
run;
%mend initial;
%initial (first = Alabama, second = AlabamaOne, third =AlabamaTwo,
fourth = AlabamaThree, final=AL_10);
%initial (first = Alaska, second = AlaskaOne, third =AlaskaTwo,
fourth = AlaskaThree, final=AK_10);
%initial (first = Arizona, second = ArizonaOne, third =ArizonaTwo,
fourth = ArizonaThree, final=AZ);
%initial (first = Arkansas, second = ArkansasOne, third =ArkansasTwo,
fourth= ArkansasThree, final=AR_10);
What I am trying to do is that in the part that puts the condition:
if countnew < 10 then delete;
I want to create a sort of do-loop that would go through the entire steps within the macro once for when countnew <10 and then creates a seperate dataset for when countnew <10 for each state....
And then does the same thing for when it's 20, 30, 40, until 70. So at the end I will have 7 differenct datasets for each state.
What is the best way to go about doing this?
Hi,
Can you post example data of what you have - in the form of a datastep, and what you want out at the end. I am not seeing anything there that can't be done in straight datastep code.
I don't understand where you want to output the multiple datasets, but what's for sure is that you have way more steps than needed.
Try something like this, which will be more efficient and easier to follow:
proc freq data=wtnodup.&first.;
tables JOBBOARDID*DATE / list out=&first. (drop = PERCENT
rename=(COUNT=COUNTNEW)
where =(COUNTNEW gt 49));
run;
data &second. (keep = COUNTNEW OLDCOUNTNEW DATE1 RATE FROM TILL JOBBOARDID RATE);
set &first.;
by JOBBOARDID DATE;
format DATE1 FROM TILL monyy7.;
DATE1 = input(cats(DATE),yymmdd8.);
FROM = lag12(DATE1);
OLDCOUNTNEW = lag12(COUNTNEW);
if lag12(JOBBOARDID) eq JOBBOARDID
and intck('month', FROM, DATE1) eq 12 then do;
TILL = DATE1;
RATE = ((COUNTNEW/OLDCOUNTNEW)-1)*100;
output;
end;
run;
proc means data=&second. noprint nway;
class DATE1;
output out=RESULT.&final. median(RATE)=MEDIANRATE;
run;
Many more optimisation tips in http://www.amazon.com/High-Performance-SAS-Coding-Christian-Graffeuille/dp/1512397490
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.