topic Re: SAS aggregate by day, weekly, month in SAS Programming

SAS aggregate by day, weekly, month

deag123 — Wed, 18 Nov 2015 14:17:46 GMT

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

Re: SAS aggregate by day, weekly, month

PaigeMiller — Wed, 18 Nov 2015 14:40:17 GMT

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;

Re: SAS aggregate by day, weekly, month

Steelers_In_DC — Wed, 18 Nov 2015 16:41:20 GMT

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;

Re: SAS aggregate by day, weekly, month

deag123 — Wed, 18 Nov 2015 20:37:46 GMT

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.

18993

18997

19149

19190

19322

18993

19002

19152

19193

19320

19000

19002

19152

19193

19320

Re: SAS aggregate by day, weekly, month

Steelers_In_DC — Wed, 18 Nov 2015 20:40:47 GMT

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
;

Re: SAS aggregate by day, weekly, month

deag123 — Wed, 18 Nov 2015 21:11:00 GMT

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

Re: SAS aggregate by day, weekly, month

Steelers_In_DC — Wed, 18 Nov 2015 21:15:30 GMT

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;

Re: SAS aggregate by day, weekly, month

deag123 — Thu, 19 Nov 2015 03:55:30 GMT

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.

Re: SAS aggregate by day, weekly, month

Steelers_In_DC — Thu, 19 Nov 2015 13:27:02 GMT

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

Re: SAS aggregate by day, weekly, month

deag123 — Thu, 19 Nov 2015 13:47:42 GMT

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

Re: SAS aggregate by day, weekly, month

deag123 — Fri, 20 Nov 2015 01:49:09 GMT

Just bumping the thread. Any solution for this?