DATA Step, Macro, Functions and more

SAS aggregate by day, weekly, month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

SAS aggregate by day, weekly, month

For the data set below(actual one is several thousand row long) I would like SAS to aggregate the income daily (many income lines everyday per machine), weekly, monthly (start of week is Monday, Start of month is 01 in any given year) by the machine. Is there a straight forward code for this? Any help is appreciated.

MachineNo Date income

1 01Jan2012 1500

1 02Jan2012 2000

1 27Aug2012 300

2 02Jan2012 1200

2 15Jun2012 50

3 03Mar2012 1000

4 08Apr2012 500


Accepted Solutions
Solution
‎11-18-2015 10:50 PM
Valued Guide
Posts: 860

Re: SAS aggregate by day, weekly, month

It's going to be difficult to correct what you are doing here because it doesn't make any sense.  here is a guess at what I think you are trying to get.  But it is not close to what I initially wrote:

 

DATA Sample2;
/*infile '/folders/myshortcuts/Data/test1.csv' dsd;*/
informat tdate date7.;
format tdate date9.;
input cust 1-1 @3 tdate date7. @11 amount;
Datalines;
1 01Jan12 20
1 05Jan12 40
1 05Jun12 40
1 16Jul12 60
1 25Nov12 10
2 01Jan12 5
2 10Jan12 4
2 08Jun12 34
2 19Jul12 23
2 23Nov12 45
3 08Jan12 23
3 10Jan12 12
3 08Jun12 78
3 19Jul12 55
3 23Nov12 34
;

data sample;
set sample2;
month=month(tdate);
week=week(tdate,'v');
run;

proc summary data=sample;
class cust week month tdate;
var amount; types cust*week cust*month cust*tdate;
output out=stats mean=;
run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,918

Re: SAS aggregate by day, weekly, month

[ Edited ]

you need to create a variable for week and month

 

month=month(date);

week=week(date,'v');

Then PROC SUMMARY does the averaging

 

proc summary;
    class machine week month date;
    var income;
types machine*week machine*month machine*date; output out=stats mean=; run;

 

Valued Guide
Posts: 860

Re: SAS aggregate by day, weekly, month

I'm not sure if this qualifies as 'straight forward code' but here is another solutions:

 

data have;
informat date date9.;
format date date9.;
input MachineNo Date income;
cards;
1 01Jan2012 1500
1 02Jan2012 2000
1 27Aug2012 300
2 02Jan2012 1200
2 15Jun2012 50
3 03Mar2012 1000
4 08Apr2012 500
;

proc sql;
create table want as
select machineno,date,income,daily,weekly,sum(income) as monthly from(
select *,sum(income) as weekly from(
select *,sum(income) as daily from(
select machineno,date,week(date) as week,month(date) as month,income
from have)
group by date)
group by week)
group by month;

Occasional Contributor
Posts: 6

Re: SAS aggregate by day, weekly, month

Thanks for the reply. Currently having some issues with reading date itself (relatively new to SAS though have done other languages). Below is the code I have. Output is given below. What should I do to get the date read correctly?

 

DATA Sample2;
input cust 1-1 @3 tdate date7. @11 amount;
Datalines;
1 01Jan12 20
1 05Jan12 40
1 05Jun12 40
1 16Jul12 60
1 25Nov12 10
2 01Jan12 5
2 10Jan12 4
2 08Jun12 34
2 19Jul12 23
2 23Nov12 45
3 08Jan12 23
3 10Jan12 12
3 08Jun12 78
3 19Jul12 55
3 23Nov12 34
;
PROC PRINT DATA=Sample2;
run;

 

Here is the output: (The date does not even look like it is from that 1960 date) It is not even consistent. Any suggestions? Thanks. 

 
cust
 
tdate
 
amount
 
111899320
211899740
311914940
411919060
511932210
62189935
72190024
821915234
921919323
1021932045
1131900023
1231900212
1331915278
1431919355
1531932034
Valued Guide
Posts: 860

Re: SAS aggregate by day, weekly, month

You have to use informat to tell SAS how the date is coming in.  You can use format to change the display to anything you want:

 

DATA Sample2;
informat tdate date7.;
format tdate date9.;
input cust 1-1 @3 tdate date7. @11 amount;
Datalines;
1 01Jan12 20
1 05Jan12 40
1 05Jun12 40
1 16Jul12 60
1 25Nov12 10
2 01Jan12 5
2 10Jan12 4
2 08Jun12 34
2 19Jul12 23
2 23Nov12 45
3 08Jan12 23
3 10Jan12 12
3 08Jun12 78
3 19Jul12 55
3 23Nov12 34
;

Occasional Contributor
Posts: 6

Re: SAS aggregate by day, weekly, month

Posted in reply to Steelers_In_DC

Thanks. That helped in reading the data. Now I am trying to get to the original problem. It is giving error again. here is the code:

 

DATA Sample2;
/*infile '/folders/myshortcuts/Data/test1.csv' dsd;*/
informat tdate date7.;
format tdate date9.;
input cust 1-1 @3 tdate date7. @11 amount;
Datalines;
1 01Jan12 20
1 05Jan12 40
1 05Jun12 40
1 16Jul12 60
1 25Nov12 10
2 01Jan12 5
2 10Jan12 4
2 08Jun12 34
2 19Jul12 23
2 23Nov12 45
3 08Jan12 23
3 10Jan12 12
3 08Jun12 78
3 19Jul12 55
3 23Nov12 34
;
PROC PRINT DATA=Sample2;
run;
month=month(tdate);
week=week(tdate,'v');
proc summary;
class cust week month tdate;
var amount; types cust*week cust*month cust*date;
output out=stats mean=;
run;

 

The errors I am getting are following. Checked the format of week, month etc. and they match. Do you find any issues. Thanks. 

 

 

 

 

80 month=month(tdate);
_____
180
 
ERROR 180-322: Statement is not valid or it is used out of proper order.
 
81 week=week(tdate,'v');
____
180
 
ERROR 180-322: Statement is not valid or it is used out of proper order.
 
82 proc summary;
83 class cust week month tdate;
ERROR: Variable WEEK not found.
ERROR: Variable MONTH not found.
84 var amount; types cust*week cust*month cust*date;
ERROR: Variable WEEK not found.
ERROR: Variable MONTH not found.
ERROR: Variable DATE not found.
85 output out=stats mean=;
86 run;
 
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.STATS may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.STATS was not replaced because this step was stopped.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
Solution
‎11-18-2015 10:50 PM
Valued Guide
Posts: 860

Re: SAS aggregate by day, weekly, month

It's going to be difficult to correct what you are doing here because it doesn't make any sense.  here is a guess at what I think you are trying to get.  But it is not close to what I initially wrote:

 

DATA Sample2;
/*infile '/folders/myshortcuts/Data/test1.csv' dsd;*/
informat tdate date7.;
format tdate date9.;
input cust 1-1 @3 tdate date7. @11 amount;
Datalines;
1 01Jan12 20
1 05Jan12 40
1 05Jun12 40
1 16Jul12 60
1 25Nov12 10
2 01Jan12 5
2 10Jan12 4
2 08Jun12 34
2 19Jul12 23
2 23Nov12 45
3 08Jan12 23
3 10Jan12 12
3 08Jun12 78
3 19Jul12 55
3 23Nov12 34
;

data sample;
set sample2;
month=month(tdate);
week=week(tdate,'v');
run;

proc summary data=sample;
class cust week month tdate;
var amount; types cust*week cust*month cust*tdate;
output out=stats mean=;
run;

Occasional Contributor
Posts: 6

Re: SAS aggregate by day, weekly, month

Thanks. Is there a way to save a table for monthly/weekly data by "cust" for all weeks/months (regardless whether there is data or not)? This is so the table becomes a bit more standardized. 

Valued Guide
Posts: 860

Re: SAS aggregate by day, weekly, month

can you give an example of the type of output you want?

Occasional Contributor
Posts: 6

Re: SAS aggregate by day, weekly, month

Posted in reply to Steelers_In_DC

Something like this would help. Obviously I haven't filled all the cells. Would like the aggregated amount by customer for all weeks and months (even if there is no data in a given month/week etc.)

 

cust week _FREQ_ Total amount
1 1   40
1 2   60
1 3   30
1 4   40
1      
1      
1      
1      
1 52   20
2 1   40
2 2   60
2 3   30
2 4   40
2      
2      
2      
2      
2 52   20
until all cust    

 

cust month _FREQ_ Total amount
1 1   40
1 2   50
1 3   30
1      
1      
1 12   30
2 1   40
2 2   30
2 3   40
2      
2      
2 12   30
until all cust    
Occasional Contributor
Posts: 6

Re: SAS aggregate by day, weekly, month

Just bumping the thread. Any solution for this?

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 3572 views
  • 1 like
  • 3 in conversation