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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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
Barite | Level 11

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
Calcite | Level 5

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
Barite | Level 11

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

deag123
Calcite | Level 5

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
Calcite | Level 5

Just bumping the thread. Any solution for this?

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
  • 10 replies
  • 25982 views
  • 1 like
  • 3 in conversation