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;
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;
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.
Many thanks to Both of you!
It works!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.