topic Re: insert a row at the end of specific rows with Totals in SAS Programming

insert a row at the end of specific rows with Totals

chithra — Mon, 03 Dec 2018 09:21:47 GMT

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

Re: insert a row at the end of specific rows with Totals

RW9 — Mon, 03 Dec 2018 09:34:37 GMT

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.

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=total+row

Re: insert a row at the end of specific rows with Totals

chithra — Mon, 03 Dec 2018 10:01:39 GMT

yes, but which doesn't fit my answer.

I need the TOTAL's in a different way.

Re: insert a row at the end of specific rows with Totals

RW9 — Mon, 03 Dec 2018 10:08:53 GMT

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;

Re: insert a row at the end of specific rows with Totals

FreelanceReinh — Mon, 03 Dec 2018 10:33:25 GMT

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;

Re: insert a row at the end of specific rows with Totals

chithra — Mon, 03 Dec 2018 10:35:01 GMT

Thanks a lot, this works.