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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.