topic Re: How to group a column based on the other common columns in SAS Enterprise Guide

How to group a column based on the other common columns?

Vigneswar — Mon, 28 Nov 2016 09:06:14 GMT

Hi,

I need to group a column based on the other common column in SAS DI.

I have below dataset as the input.

INPUT DATA:

Product date customer sales org actual

10 01/10/2015 124 IT10 110

10 01/10/2015 124 IT11 213

I want the below output,

OUTPUT:

Product date customer sales org

10 01/10/2015 124 IT10 323

Can anyone give suggestion on this?

Thanks in advance!!!

Vigneswar

How to group a column based on the other common columns

Vigneswar — Mon, 28 Nov 2016 09:20:16 GMT

Hi,

I need to group a column based on the other common column in SAS DI.

I have below dataset as the input.

INPUT DATA:

Product date customer sales org actual

10 01/10/2015 124 IT10 110

10 01/10/2015 124 IT11 213

I want the below output,

OUTPUT:

Product date customer sales org

10 01/10/2015 124 IT10 323

Can anyone give suggestion on this?

Thanks in advance!!!

Vigneswar

Re: How to group a column based on the other common columns

Shmuel — Mon, 28 Nov 2016 09:57:04 GMT

Assuming data is already sorted by: product date customer and sales org:

data wany;

set have;

by product date customer;

retain first_org total;

if first.customer then do;

first_org = sales_org;

total = actual;

end;

else total = sum(of total, actual);

if last.customer then do;

sales_org = first_org;

actual = total;

output;

end;

run;

Re: How to group a column based on the other common columns

Kurt_Bremser — Mon, 28 Nov 2016 10:23:59 GMT

A slightly different data step solution would be this:

data have;
input product date :ddmmyy10. customer sales_org $ actual;
format date ddmmyy10.;
cards;
10 01/10/2015 124 IT10 110
10 01/10/2015 124 IT11 213
;
run;

data want;
set have (rename=(sales_org=_sales_org actual=_actual));
by product date customer;
retain sales_org;
if first.customer then sales_org = _sales_org;
actual + _actual;
if last.customer then output;
drop _sales_org _actual;
run;

Note that the basic logic is the same as @Shmuel's

Re: How to group a column based on the other common columns

slchen — Mon, 28 Nov 2016 13:22:59 GMT

proc sql;
select distinct product,date,customer,min(sales_org) as sales_org, sum(actual) as actual from have group by product;
quit;