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 😃
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
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;
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.