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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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