Date based Query in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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
Grand Advisor
Posts: 9,567

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/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;

View solution in original post


All Replies
Solution
‎07-06-2015 09:00 AM
Grand Advisor
Posts: 9,567

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/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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 173 views
  • 0 likes
  • 2 in conversation