I am working on this daily report and try to find a way to get what I want. So here is the example:
If Today(the date I run SAS) is Monday, Sep 7, 2015 , then I need the 'Want' column. The challenge part is the Friday, September 04, 2015's want number, I need it to be the sum of Have column from Friday Sep 04 to Sunday Sep 06th.
Is it possible to get it?
Date | Have | Want |
Tuesday, September 01, 2015 | 2 | 2 |
Wednesday, September 02, 2015 | 3 | 3 |
Thursday, September 03, 2015 | 4 | 4 |
Friday, September 04, 2015 | 5 | 12 |
Saturday, September 05, 2015 | 2 | 0 |
Sunday, September 06, 2015 | 5 | 0 |
Thanks
Here is one way to do this:
data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;
proc sql;
create table Fridays as
select intnx("WEEK.6", date, 0) as date, sum(have) as want
from have
where weekday(date) in (1,6,7)
group by calculated date;
quit;
data want;
merge have Fridays(in=friday);
by date;
if not friday then want = have;
if weekday(date) in (1, 7) then want = 0;
run;
proc print data=want; run;
Here is one way to do this:
data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;
proc sql;
create table Fridays as
select intnx("WEEK.6", date, 0) as date, sum(have) as want
from have
where weekday(date) in (1,6,7)
group by calculated date;
quit;
data want;
merge have Fridays(in=friday);
by date;
if not friday then want = have;
if weekday(date) in (1, 7) then want = 0;
run;
proc print data=want; run;
here is another way;
data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;
proc sort; by descending date;
data want; set have;
day=weekday(date); drop day sum;
want=have;
if weekday(date)=1 then do; sum+have; want=0; end; *if sunday;
if weekday(date)=7 then do; sum+have; want=0; end; *if saturday;
if weekday(date)=6 then do; sum+have; want=sum; sum=0; end; *if friday;
proc sort; by date;
proc print; run;
@Jim_G, Nice and simple solution, as long as there are no holes in the time series. Try it, for example, with
data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
2015-09-12 2
2015-09-13 5
;
Thank you so much PS stats! It works perfect!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.