- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your help, much appreciated.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;