DATA Step, Macro, Functions and more

summing across rows conditional on columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

summing across rows conditional on columns

Hi, I have a small practice data set where there are three variables such as item_code (character variable) with repeated instances of sales (numeric) as rows of the item_code and for item_code there can be  two different type, A and B (also character variable). I'm trying to sum by unique item_code the sum of type A and type B and then the sum of both together (see data want below).

I'd greatly appreciate if someone could assist me. 

 

data have;

input item_code $ type $ sales;

datalines;

V1 A 3

V1 A 6

V2 A 2

V2 B 1

V2 A 5

V3 A 5

V3 B 1

V3 B 7

;

run;

 

/* data want;

Item_code  Sum_typeA   Sum_typeB   Sum_all

V1                      9                    0                 9

V2                      7                    1                 8

V3                      5                    8                13

 


Accepted Solutions
Solution
3 weeks ago
Super Contributor
Posts: 345

Re: summing across rows conditional on columns

Posted in reply to Phil_from_PGA
data want(keep=item_code sum_:);
   set have;
   by item_code;

   length sum_typeA sum_typeB sum_all 8;
   retain sum_:;

   if first.item_code then do;
      sum_typeA = 0;
      sum_typeB = 0;
   end;

   if type = 'A' then sum_typeA = sum_typeA + sales;
   else sum_typeB = sum_typeB + sales;

   if last.item_code then do;
      sum_all = sum_typeA + sum_TypeB;
      output;
   end;
run;

View solution in original post


All Replies
Super Contributor
Posts: 345

Re: summing across rows conditional on columns

Posted in reply to Phil_from_PGA
Proc summary can do this, if you really need that format in a dataset, you have to transpose the procedures output-dataset. You can, of course, using by-group-processing with retain and do the calculation manually.
Occasional Contributor
Posts: 15

Re: summing across rows conditional on columns

Posted in reply to andreas_lds

How?

Solution
3 weeks ago
Super Contributor
Posts: 345

Re: summing across rows conditional on columns

Posted in reply to Phil_from_PGA
data want(keep=item_code sum_:);
   set have;
   by item_code;

   length sum_typeA sum_typeB sum_all 8;
   retain sum_:;

   if first.item_code then do;
      sum_typeA = 0;
      sum_typeB = 0;
   end;

   if type = 'A' then sum_typeA = sum_typeA + sales;
   else sum_typeB = sum_typeB + sales;

   if last.item_code then do;
      sum_all = sum_typeA + sum_TypeB;
      output;
   end;
run;
Occasional Contributor
Posts: 15

Re: summing across rows conditional on columns

Posted in reply to andreas_lds

Thank you for your help, much appreciated.

Frequent Contributor
Posts: 112

Re: summing across rows conditional on columns

Posted in reply to Phil_from_PGA

Just one another way to do this. Perhaps not the most optimum, but if you are more comfortable with Proc SQL and transpose rather than do group processing.

proc sql;
create table want_ as select item_code, type, sum(sales) as sum_type from have group by item_code, type;
quit;

proc transpose data = want_ out = want_1 (drop =_name_);
by item_code;
var sum_type;
id type;
run;

proc sql;
create table want_2 as select item_code, sum(sales) as sum_all from have group by item_code;
quit;

Proc sql;
Create table want_final as select a.item_code, a.A as sum_typeA, a.B as sum_typeB, b.sum_all from want_1 a join want_2 b on a.item_code = b.item_code;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 160 views
  • 0 likes
  • 3 in conversation