Dear All,
I have a dataset like the below :
A1 | A | B |
IME_ANNUI | 4 | 501 |
IME_ANNUI | 2 | 200 |
IME_PREM | 11 | 101 |
IME_TOTAL | 11 | 100 |
IME_TOTAL | 3 | 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.
A1 | A | B |
IME_ANNUI | 4 | 501 |
IME_ANNUI | 2 | 701 |
IME_PREM | 11 | 101 |
IME_TOTAL | 11 | 100 |
IME_TOTAL | 3 | 202 |
Could anyone please help me here?
Thanks,
Chithra
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;
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;
Thanks..This works
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;
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;
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.