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
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;
How?
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;
Thank you for your help, much appreciated.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.