Date based Query in SAS

[Ganeshk]

Hi All,

My data is as shown below:

Promo_id Promotion_Start Promotion_End

10121 1/1/2015 1/14/2015

10129 1/1/2015 1/7/2015

So Requirement is to convert my data into weekly level:

Note: As per my client week start is of Monday to Sunday basis

Ex: Output of my data with new column need to be created "New Start week", "Count_of_Days" and "Calc_Reqd"

Promo_id Promotion_Start Promotion_End New_Start_week Count_of_Days "Calc_Reqd"

10121 1/1/2015 1/14/2015 12/29/2014 4 "4/7"

10121 1/1/2015 1/14/2015 1/5/2015 7 "7/7"

10121 1/1/2015 1/14/2015 1/12/2015 3 "3/7"

10129 1/1/2015 1/7/2015 12/29/2014 4 "4/7"

10129 1/1/2015 1/7/2015 1/5/2015 3 "3/7"

Thanks,

Ganesh K

[Ksharp · Accepted solution]

Code: Program

data have;
input Promo_id Promotion_Start : mmddyy10. Promotion_End : mmddyy10.;
format Promotion_Start Promotion_End mmddyy10.;
cards;
10121 1/1/2015 1/14/2015
10129 1/1/2015 1/7/2015
;
run;
data temp;
 set have;
 do date=Promotion_Start to Promotion_End ;
  New_Start_week=intnx('week.2',date,0,'b');
  output;
 end;
 format date New_Start_week mmddyy10.;
run;
data want;
 set temp;
 by Promo_id New_Start_week;
 length Calc_Reqd $ 8;
 if first.New_Start_week then Count_of_Days=0;
 Count_of_Days +1;
 if last.New_Start_week then do;
   Calc_Reqd=cats(Count_of_Days,'/7');
   output;
 end;
 drop date;
run;