topic Doing a do loop within SAS Macro in SAS Programming

Doing a do loop within SAS Macro

mustafghan — Thu, 08 Oct 2015 23:11:52 GMT

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?

Re: Doing a do loop within SAS Macro

RW9 — Fri, 09 Oct 2015 09:30:03 GMT

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.

Re: Doing a do loop within SAS Macro

LinusH — Sun, 11 Oct 2015 13:31:34 GMT

Is there a reason for keeping data in separate data sets? By having all data in one place usually makes life easier, and probably make macro logic obsolete.

Re: Doing a do loop within SAS Macro

ChrisNZ — Mon, 12 Oct 2015 22:06:14 GMT

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