SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Phil_from_PGA
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19
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

5 REPLIES 5
andreas_lds
Jade | Level 19
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.
andreas_lds
Jade | Level 19
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;
Phil_from_PGA
Calcite | Level 5

Thank you for your help, much appreciated.

devsas
Pyrite | Level 9

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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