BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chithra
Quartz | Level 8

Dear All,

 

I have a dataset like the below :

 

A1AB
IME_ANNUI4501
IME_ANNUI2200
IME_PREM11101
IME_TOTAL11100
IME_TOTAL3

102

 

and I need the output as follows keeping A1 in ascending and A in descending order and B as sum of(B) with respect to each row.

 

A1AB
IME_ANNUI4501
IME_ANNUI2701
IME_PREM11101
IME_TOTAL11100
IME_TOTAL3202

 

 

Could anyone please help me here?

 

Thanks,

Chithra

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

Please post input data as datastep using datalines-statement, so the we start with something that matches your data.

 

This is one solution:

 

data work.have;
   length A1 $ 20 A B 8;

   input A1 A B;

   datalines;
IME_ANNUI 4 501
IME_ANNUI 2 200
IME_PREM 11 101
IME_TOTAL 11 100
IME_TOTAL 3 102
;
run;

proc sort data=work.have out=work.haveSorted presorted;
   by A1 descending A;
run;


data work.want;
   set have;
   by A1;

   length bSum 8;
   retain bSum;

   if first.A1 then do;
      bSum = 0;
   end;

   bSum = bSum + B;

   keep A1 A bSum;
   rename bSum = B;
run;

View solution in original post

4 REPLIES 4
andreas_lds
Jade | Level 19

Please post input data as datastep using datalines-statement, so the we start with something that matches your data.

 

This is one solution:

 

data work.have;
   length A1 $ 20 A B 8;

   input A1 A B;

   datalines;
IME_ANNUI 4 501
IME_ANNUI 2 200
IME_PREM 11 101
IME_TOTAL 11 100
IME_TOTAL 3 102
;
run;

proc sort data=work.have out=work.haveSorted presorted;
   by A1 descending A;
run;


data work.want;
   set have;
   by A1;

   length bSum 8;
   retain bSum;

   if first.A1 then do;
      bSum = 0;
   end;

   bSum = bSum + B;

   keep A1 A bSum;
   rename bSum = B;
run;
chithra
Quartz | Level 8

Thanks..This works

mkeintz
PROC Star

You want to maintain a cumulative sum of B, resetting it at the start of each A1:

 

data have;
  input A1 $9.	A	B;
datalines;
IME_ANNUI	4	501
IME_ANNUI	2	200
IME_PREM	11	101
IME_TOTAL	11	100
IME_TOTAL	3	102
run;
data want (drop=sumb);
  set have;
  by a1;
  sumb+b;
  if not first.a1 then b=sumb;
  else sumb=b;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
kiranv_
Rhodochrosite | Level 12

if your data is already sorted you can do

data want(rename=(_B=B));
set have;
by A1;
if first.A1 then _B= B;
else _B+B;
drop B;
run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 4 replies
  • 1825 views
  • 1 like
  • 4 in conversation