BookmarkSubscribeRSS Feed
sarang
Fluorite | Level 6

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;

3 REPLIES 3
gergely_batho
SAS Employee

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
Super User

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
Fluorite | Level 6

Many thanks to Both of you!

It works!

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 1561 views
  • 9 likes
  • 3 in conversation