topic Re: Collapsing rows given several conditions and applying different functions in SAS Programming

Collapsing rows given several conditions and applying different functions

Banker337 — Tue, 03 Aug 2021 15:33:40 GMT

Good day everyone !

Currently I'm solving one complicated task and got stuck.

Better explain on example:

I have:

ID	Customer	Date	PROD	SUM	Digital	Random field
ID1	C1	05.05.2021	P1	15	1	AAA
ID2	C1	05.05.2021	P1	16	0	AAA
ID3	C2	06.05.2021	P2	21	1	BBB

Given this data set I need to collapse rows when Customer, Date and Prod are equal. In fact, it is one deal, written on a different rows. Field SUM I need to sum up when collapsing, Digital - need to take average of rows. Random Field rows are identical in a group of deals.

As an output:

Customer	Date	PROD	SUM	Digital	Random field	Count rows
C1	05.05.2021	P1	31	0.5	AAA	2
C2	06.05.2021	P2	21	1	BBB	1

Please help, this is really complicated to me 😃

Re: Collapsing rows given several conditions and applying different functions

Banker337 — Tue, 03 Aug 2021 17:05:23 GMT

Well, I figured it out)

proc sql noprint; 
  create table output as 
    select Customer, Date, Prod, 
		Randomfield
      ,sum(Sum) as Sum 
      ,avg(Digital) as Digital
      ,count(*) as count
  from work.data_test
  group by Customer, Date, Prod 
;quit;

Re: Collapsing rows given several conditions and applying different functions

sbxkoenk — Tue, 03 Aug 2021 17:12:40 GMT

Not sure if your date is DDMMYY or MMDDYY. Here in Europe it's DDMMYY.

data have;
input 
ID $ Customer $ Date : ddmmyy10. PROD $ SUM Digital Random_field $;
format Date date9.;
cards;
ID1	C1	05.05.2021	P1	15	1	AAA
ID2	C1	05.05.2021	P1	16	0	AAA
ID3	C2	06.05.2021	P2	21	1	BBB
;
run;

PROC MEANS data=have(drop=ID) SUM MEAN nway noprint;
 CLASS Customer Date PROD;
 var SUM Digital;
 output out=want(drop=_TYPE_ SUM_mean Digital_sum) sum= mean= / autoname;
run;

proc print; run;
/* end of program */

Regards,

Koen

Re: Collapsing rows given several conditions and applying different functions

Cynthia_sas — Tue, 03 Aug 2021 17:38:36 GMT

Hi:

And to add to the list of possible solutions, here's a PROC REPORT solution:

And, TABULATE would do it too, although the arrangement of variables needs to change a bit for TABULATE to do the collapsing and summarizing:

Hope this gives you more ideas.

Cynthia