SAS Loop with Multiple dates

Question

Updated** I am relatively new to SAS and am having an issue looping through a date variable. At the most basic level, I need to iteratively create multiple data sets (or an iterative concatenation.) I am able to create a static dataset, but am having a problem with looping. Here is the block of code that works

`
%let myvar='11Jul16'd;

     data shape_test;

     set Analysis_set;

     Where(dt_expctd_setmt >&myvar and dt_trd <= &myvar);


     by  dt_trd; 



IF B='.' Then B=0;

IF I='.' Then I=0;

IF S='.' Then S=0;

B=sum(B); I=Sum(I); S=Sum(S); 

S_B= S-B;

S_B_I=S-B+I;

format B I S S_B S_B_I dollar12.0;

drop dt_expctd_setmt;

run;`

I would like to loop through a list of dates that would produce the one data set for each date, or stack each date on the previous.

I had something like this in mind, but can not properly access the ith entry in the date vector I am trying to loop through:

%let date_var= the date column; 
Do i = 1 to length(%date_var);

%macro PleaseWork(date_var);

Data Project_name&date_var(i);

set Analysis_set;

Where(dt_expctd_setmt >&date_var(i) and dt_trd <= &date_var(i));

Code with all the math stuff (like above)

Run;

%mend
End;

I hope this is clearer! Thank you again for your help!!

I'm having a hard time seeing this problem. Can you include some sample data and expected output? — Reeza

Tom Tom · Accepted Answer · 2016-08-16T12:43:46

If your list of dates is in a table then join that table with the analysis data and produce the result in a single query. No need for macro logic.

proc sql;
  create table total_break_out as 
     select b.myvar As Date
          , a.category_name AS Category
          , sum(a.B) as B
          , sum(a.I) as I 
          , sum(a.S) as S
     from analysis_set a
        , dateset b 
     where a.var1 > b.myvar
       and a.var2 <= b.myvar
     group by 1,2
   ;
quit;

SAS Loop with Multiple dates

1 Answers