topic Re: Taking the average for each specific period in a time series dataset in SAS Procedures

Taking the average for each specific period in a time series dataset

m1986MM — Sun, 30 Nov 2014 01:50:20 GMT

Hello everyone,

I have a huge dataset with more than 30m observations. The main structure of the dataset is similar to table below. However, the number of variables are 46 (not all of them useful).

Company_Nm	size	Brach	product	date	cost
X	X1	A
X	X2	B
X	X3	C
X	X4	D
Y	Y1	A
Y	Y2	B
Y	Y3	C
Y	Y4	D
Y	Y5	E
Z	Z1	A

In this dataset what I want to analyze is the cost of different product, offered by different branches of each company. The data for cost is available since 1995 in a weekly order (not similar day of the week for all of the observations).

Now what I want to do is taking the average cost for each product, offered by each branch, for every month. In this way I can both reduce the number on obs and analyze the price variable cross sectionally.

The date viable is in this format: YYYY-MM-DD. Because of the large number of variables, I prefer to not use CLASS statement. However, even if I have to use CLASS, how can I write the code to take the average for every month and create a dataset that has date (YYYY-MM) and Ave_price instead of the last two variables in the current data set.

I greatly appreciate any and all suggestions.

Re: Taking the average for each specific period in a time series dataset

Jagadishkatam — Sun, 30 Nov 2014 02:52:43 GMT

This is a untested code, however i believe it will work for you , could you please check with you data

proc sql;

create table want as select product,branch,month,ym,avg(cost) as avg_cost from (select *, month(date) as month, put(date,yymmd.) as ym from test) group by product,branch,month,ym;

run;

Thanks,

Jag

Re: Taking the average for each specific period in a time series dataset

Patrick — Sun, 30 Nov 2014 04:42:59 GMT

Below should work.

data have;

infile datalines truncover;

input Company_Id $ Branch_Id $ Prod_Id $;

format Date yymmdd10. cost dollar10.2;

Date='01Jan2012'd;

Date=intnx('week',date,0,'b');

do while (date<today());

cost=date/1000*_n_;

output;

Date=intnx('week',date,1,'b');

end;

datalines;

1 1 A

1 1 B

1 2 A

2 1 B

;

run;

proc sql;

create table want as

select

Company_Id

,Branch_Id

,Prod_Id

,intnx('month',Date,0,'b') as Month_Begin_Dt format=yymm7.

,avg(Cost) as avg_cost format=dollar10.2

from have

group by

Company_Id

,Branch_Id

,Prod_Id

,calculated Month_Begin_Dt

;

quit;

Re: Taking the average for each specific period in a time series dataset

m1986MM — Sun, 30 Nov 2014 05:48:52 GMT

Thanks Patrick. Could you please explain below statements in the code?

1. Date='01Jan2012'd;

2. cost=date/1000*_n_;

Thanks.

Re: Taking the average for each specific period in a time series dataset

Patrick — Sun, 30 Nov 2014 08:03:07 GMT

That's just about mocking up some sample data. In 1) I need to start with some date and in 2) I just need to populate "cost" with some value.

Re: Taking the average for each specific period in a time series dataset

m1986MM — Sun, 30 Nov 2014 23:55:10 GMT

Thanks.