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!
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.
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.