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

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_Nmsize Brachproductdatecost
XX1A
XX2B
XX3C
XX4D
YY1A
YY2B
YY3C
YY4D
YY5E
ZZ1A

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

View solution in original post

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
Patrick
Opal | Level 21

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;

m1986MM
Obsidian | Level 7

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

1.  Date='01Jan2012'd;

2.  cost=date/1000*_n_;

Thanks.


Patrick
Opal | Level 21

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1167 views
  • 3 likes
  • 3 in conversation