topic Re: aggregating data by in SAS Data Management

aggregating data by

sarang — Tue, 03 Mar 2015 06:30:45 GMT

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_price).

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;

Re: aggregating data by

gergely_batho — Tue, 03 Mar 2015 13:45:46 GMT

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;

Re: aggregating data by

Kurt_Bremser — Tue, 03 Mar 2015 13:54:30 GMT

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.

Re: aggregating data by

sarang — Tue, 03 Mar 2015 16:48:39 GMT

Many thanks to Both of you!

It works!