Solved
Contributor
Posts: 31

# Date based Query in SAS

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

Accepted Solutions
Solution
‎07-06-2015 09:00 AM
Super User
Posts: 10,766

## Re: Date based Query in SAS

### 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/201510129 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; `

All Replies
Solution
‎07-06-2015 09:00 AM
Super User
Posts: 10,766

## Re: Date based Query in SAS

### 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/201510129 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; `
🔒 This topic is solved and locked.