SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

aggregating data by

Reply
New Contributor
Posts: 4

aggregating data by

Hi,

I'm trying to aggregate individual purchase data.

The data set contains all information about where, when, and what each individual purchased a product.

It consists of more than 10 columns.

When I used code to aggregate the data, I get only 6 columns (id, week, retailer, product_code, quantity, total...

However, I want to keep all other columns as well.

To keep all columns, I used . I wonder if it is a right approach. Unfortunately, and have different results with different numbers of rows.

How can I solve this problem?

Jason

proc sql;

create table work.Panel_purchase as

select id, week, retailer, product_code, sum(quantity) as quantity, sum(total_price) as total_price

from work.Purchase_all

group by id, week, retailer, product_code

order by id, week, retailer, product_code;

quit;

proc sql;

create table work.Panel_purchase as

select *, sum(quantity) as quantity, sum(total_price) as total_price

from work.Purchase_all

group by id, week, retailer, product_code

order by id, week, retailer, product_code;

quit;

proc sort data=Work.Panel_purchase out=Work.Panel_purchase nodupkey;

  by id week retailer product_code quantity total_price_paid;

run;

SAS Employee
Posts: 340

Re: aggregating data by

List all your variables (that are not aggregated) in the group by clause.

proc sql;

create table work.Panel_purchase as

select id, week, retailer, product_code, sum(quantity) as quantity, sum(total_price) as total_price

from work.Purchase_all

group by id, week, retailer, product_code, id2, month, col3, col4 /*etc...*/

order by id, week, retailer, product_code;

quit;

Super User
Posts: 6,936

Re: aggregating data by

In the select part, keep all of the variables EXCEPT those that you will summarize, and add the sum() for the variables to be summarized; add all non-summarized variables to the group by part.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: aggregating data by

Many thanks to Both of you!

It works!

Ask a Question
Discussion stats
  • 3 replies
  • 339 views
  • 9 likes
  • 3 in conversation