Dear All,
I have a dataset like below;
data have;
input a1 $12. a b;
cards;
IME_PREM_ADJ 11 100
IME_PREM_ADJ 12 102
IME_PREM_NON 11 101
IME_PREM_ADJ 13 101
IME_PREM_RES 11 105
IME_PREM_RES 3 102
IME_PREM_NON 2 200
IME_ANNUITIE 3 205
IME_ANNUITIE 4 501
;
run;
I need to sum the b row using a1 by group, and insert a row of TOTAL in the a column for each a1 by group with the sum.
The output will be :
IME_ANNUITIE | 3 | 205 |
IME_ANNUITIE | 4 | 501 |
IME_ANNUITIE | TOTAL | 706 |
IME_PREM_ADJ | 11 | 100 |
IME_PREM_ADJ | 12 | 102 |
IME_PREM_ADJ | 13 | 101 |
IME_PREM_ADJ | TOTAL | 303 |
IME_PREM_NON | 11 | 101 |
IME_PREM_NON | 2 | 200 |
IME_PREM_NON | TOTAL | 301 |
IME_PREM_RES | 11 | 105 |
IME_PREM_RES | 3 | 102 |
IME_PREM_RES | TOTAL | 207 |
Thanks in advance,
Chithra
data have; input a1 $12. a b; cards; IME_PREM_ADJ 11 100 IME_PREM_ADJ 12 102 IME_PREM_NON 11 101 IME_PREM_ADJ 13 101 IME_PREM_RES 11 105 IME_PREM_RES 3 102 IME_PREM_NON 2 200 IME_ANNUITIE 3 205 IME_ANNUITIE 4 501 ; run; data want; set have; by a1 notsorted; retain cnt; cnt=ifn(first.a1,b,sum(cnt,b)); if not last.a1 then output; else do; output; a1="TOTAL"; b=cnt; output; end; run;
Please use the search functionality before posting a question, I can think of at least two questions from last week which were more or less the same as this.
yes, but which doesn't fit my answer.
I need the TOTAL's in a different way.
data have; input a1 $12. a b; cards; IME_PREM_ADJ 11 100 IME_PREM_ADJ 12 102 IME_PREM_NON 11 101 IME_PREM_ADJ 13 101 IME_PREM_RES 11 105 IME_PREM_RES 3 102 IME_PREM_NON 2 200 IME_ANNUITIE 3 205 IME_ANNUITIE 4 501 ; run; data want; set have; by a1 notsorted; retain cnt; cnt=ifn(first.a1,b,sum(cnt,b)); if not last.a1 then output; else do; output; a1="TOTAL"; b=cnt; output; end; run;
Thanks a lot, this works.
Hi @chithra,
Try this:
proc sort data=have;
by a1;
run;
proc format;
value totalf
.='TOTAL';
run;
data want(drop=tb);
do until(last.a1);
set have;
by a1;
tb=sum(tb,b);
output;
end;
a=.;
b=tb;
output;
format a totalf.;
run;
proc print data=want noobs;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: