SAS aggregate by day, weekly, month

[deag123]

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

[PaigeMiller]

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;
    

[Steelers_In_DC]

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;

[deag123]

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

1

1

18993

20

2

1

18997

40

3

1

19149

40

4

1

19190

60

5

1

19322

10

6

2

18993

5

7

2

19002

4

8

2

19152

34

9

2

19193

23

10

2

19320

45

11

3

19000

23

12

3

19002

12

13

3

19152

78

14

3

19193

55

15

3

19320

34

[Steelers_In_DC]

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
;

[deag123]

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

[Steelers_In_DC · Accepted solution]

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;

[deag123]

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. 

[Steelers_In_DC]

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

[deag123]

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

[deag123]

Just bumping the thread. Any solution for this?