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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.