Desktop productivity for business analysts and programmers

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

Reply
New Contributor
Posts: 2

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

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

New Contributor
Posts: 2

How to group a column based on the other common columns

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

Super User
Posts: 1,227

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

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;

Esteemed Advisor
Posts: 6,646

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 275

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


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

Ask a Question
Discussion stats
  • 4 replies
  • 156 views
  • 0 likes
  • 4 in conversation