similar to other solution....
data have;
input api_date:mmddyy10. api_count;
format api_date mmddyy10. api_count 8.0;
datalines;
1/1/2017 125
8/2/2017 126
8/3/2017 127
8/4/2017 128
8/5/2017 129
8/6/2017 130
8/7/2017 131
8/8/2017 132
8/9/2017 133
8/10/2017 134
8/11/2017 135
8/12/2017 136
8/13/2017 137
8/14/2017 138
8/15/2017 139
8/16/2017 140
8/17/2017 141
8/18/2017 142
8/19/2017 143
8/20/2017 144
8/21/2017 145
8/22/2017 146
8/23/2017 147
8/24/2017 148
8/25/2017 149
8/26/2017 150
8/27/2017 151
8/28/2017 152
8/29/2017 153
8/30/2017 154
8/31/2017 155
9/1/2017 156
9/2/2017 157
9/3/2017 158
9/4/2017 159
9/5/2017 160
9/6/2017 161
;
run;
proc sql;
create table want as select
intnx('month',api_date,case when api_date>=nwkdom(5,6,month(api_date),year(api_date)) then 1 else 0 end,'begin') format mmddyy10. as Period,
case
/*if api_date >= last saturday of current month then use last saturday of current month*/
when api_date >=nwkdom(5,6,month(api_date),year(api_date)) then nwkdom(5,6,month(api_date),year(api_date))
/*else use last saturday of previous month*/
else nwkdom(5,6,month(intnx('month',api_date,-1,'same')),year(intnx('month',api_date,-1,'same')))
end format mmddyy10. as PeriodStart,
case
/*if api_date >= last saturday of current month then use last saturday of next month*/
when api_date >=nwkdom(5,6,month(api_date),year(api_date)) then nwkdom(5,6,month(intnx('month',api_date,1,'same')),year(intnx('month',api_date,1,'same')))
/*else use last saturday of current month*/
else nwkdom(5,6,month(api_date),year(api_date))
end
/*and backup one day from that*/
-1 format mmddyy10. as PeriodEnd,
sum(api_count) as Total_API_Count
from
have
group by
calculated period
,calculated periodstart
,calculated periodEnd
;
quit;
... View more
