BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
last_one
Calcite | Level 5

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?

 

 DateHave Want
Tuesday, September 01, 201522
Wednesday, September 02, 201533
Thursday, September 03, 201544
Friday, September 04, 2015512
Saturday, September 05, 201520
Sunday, September 06, 201550

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

8 REPLIES 8
Reeza
Super User
How are you defining the rules? Is it just for this Week's data or all weeks? Are all Fridays only for the weekend?
PGStats
Opal | Level 21

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;
PG
Jim_G
Pyrite | Level 9

 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;

PGStats
Opal | Level 21

@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
;
PG
Jim_G
Pyrite | Level 9
Thanks @PGStats I thought about a little error message if the days were not consecutive.
last_one
Calcite | Level 5

Thank you so much PS stats! It works perfect!

last_one
Calcite | Level 5
@Reeza, it is for all Fridays if the day I run SAS is on Monday.
last_one
Calcite | Level 5
@PGStats, thank you! I will try it when back to work on Monday.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1159 views
  • 1 like
  • 4 in conversation