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

[Vigneswar]

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

[Vigneswar]

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

[Shmuel]

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;

[Kurt_Bremser]

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

[slchen]

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