topic Re: Grouping data with BY statement in SAS Programming

Grouping data with BY statement

Uknown_user — Thu, 20 Apr 2017 09:09:04 GMT

DATA TMP;
SET NOV_TRX;
BY PK_CONSUMER_ID;
IF FIRST.PK_CONSUMER_ID THEN TOTAL_VOLUME = 0;
TOTAL_VOLUME + SALES_QUANTITY;
IF LAST.PK_CONSUMER_ID THEN OUTPUT;
RUN;

Hello everyone,

I am trying to group a piece of data set (please see the attachement that representes a sample of my data). The code I wrote so far provides me with TOTAL_VOLUME per consumer, however, I would also need the volume per customer and VP_MG as well as volume per customer and Diesel_Petrol but do not know how to amend it code so that it worked. Thanks for suggestions.

Re: Grouping data with BY statement

Reeza — Thu, 20 Apr 2017 10:39:38 GMT

Look at PROC MEANS, specifically look at the CLASS, WAYS, TYPES statements.

Re: Grouping data with BY statement

Uknown_user — Thu, 20 Apr 2017 10:57:37 GMT

Thanks for your suggestion, I might take a look at it later. I would like to manage it just with BY statement right now which I believe should be very easy.

Re: Grouping data with BY statement

Kurt_Bremser — Thu, 20 Apr 2017 11:41:35 GMT

DO NOT post SAS data in an Excel file. An Excel spreadsheet contains no metadata information (variable length, assigned formats etc) which is crucial for understanding your issues.

Instead use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your dataset into a data step that can be posted here in a code window. This allows to exactly recreate your dataset with copy/paste and run, and since only text is involved, firewalls won't block this. (I can't download Excel files from the internet where my SAS is available, because the company firewall blocks them for security reasons; the same is true for many contributors here)

Since you have two different subgroups, you will need two steps if you use data step logic:

data
  sum_total (keep=pk_customer_id total_volume)
  sum_vp_mg (keep=pk_customer_id vp_mg vp_mg_volume)
;
set nov_trx;
by pk_customer_id vp_mg;
if first.pk_customer_id then total_volume = 0;
if first.vp_mg then vp_mg_volume = 0;
total_volume + sales_quantity;
vp_mg_volume + sales_quantity;
if last.vp_mg then output sum_vp_mg;
if last.pk_customer_id then output sum_total;
run;

data sum_diesel_petrol (keep=pk_customer_id diesel_petrol dp_volume);
set nov_trx;
by pk_customer_id diesel_petrol;
if first.diesel_petrol then dp_volume = 0;
dp_volume + sales_quantity;
if last.diesel_petrol then output;
run;

Re: Grouping data with BY statement

Uknown_user — Thu, 20 Apr 2017 11:44:53 GMT

Thanks Kurt for your recommendation. The code worked fine.