aggregating data by

[sarang]

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;

[gergely_batho]

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;

[Kurt_Bremser]

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.

[sarang]

Many thanks to Both of you!

It works!