## summing across rows conditional on columns

Solved
Occasional Contributor
Posts: 15

# 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
‎10-30-2017 05:52 PM
Valued Guide
Posts: 580

## Re: summing across rows conditional on columns

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;

All Replies
Valued Guide
Posts: 580

## Re: summing across rows conditional on columns

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

How?

Solution
‎10-30-2017 05:52 PM
Valued Guide
Posts: 580

## Re: summing across rows conditional on columns

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

Thank you for your help, much appreciated.

Frequent Contributor
Posts: 121

## Re: summing across rows conditional on columns

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.