topic Re: summing across rows conditional on columns in SAS Programming

summing across rows conditional on columns

Phil_from_PGA — Mon, 30 Oct 2017 04:53:47 GMT

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

Re: summing across rows conditional on columns

andreas_lds — Mon, 30 Oct 2017 05:22:16 GMT

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.

Re: summing across rows conditional on columns

Phil_from_PGA — Mon, 30 Oct 2017 05:56:04 GMT

How?

Re: summing across rows conditional on columns

andreas_lds — Mon, 30 Oct 2017 07:03:14 GMT

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;

Re: summing across rows conditional on columns

Phil_from_PGA — Mon, 30 Oct 2017 21:52:15 GMT

Thank you for your help, much appreciated.

Re: summing across rows conditional on columns

devsas — Wed, 01 Nov 2017 15:22:58 GMT

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;