topic Re: Replace dot(.) with sum value of each observation. in SAS Procedures

Replace dot(.) with sum value of each observation.

rohit_prajapati — Mon, 08 Oct 2012 10:00:04 GMT

Hi ALL,

Have -

DATA TEST;
INPUT SUBJECT $ NUM1 NUM2 NUM3 NUM4 NUM5;
DATALINES;
A01 . 2 5 36 .
B01 65 25 54 . 3
C01 4 . . 4 5
D01 . . 5 4 5
E01 . . . 5 4
RUN;

Want -

SUBJECT	NUM1	NUM2	NUM3	NUM4	NUM5	SUM_NUM
A01	43	2	5	36	43	43
B01	65	25	54	147	3	147
C01	4	13	13	4	5	13
D01	14	14	5	4	5	14
E01	9	9	9	5	4	9

The value of SUM_NUM has to be place where you can see ' . ' (dot)

Example - For subject A01 NUM1 has dot (.) which replace the value of SUM_NUM.

Kindly make sure that there are n number of NUM coloumn.

Thanks in Advance.

Regards,

Rohit

Re: Replace dot(.) with sum value of each observation.

Amir — Mon, 08 Oct 2012 11:44:15 GMT

Hi Rohit,

Does the following do what you want:

data want;

set test;

sum_num=sum(of num1-num5);

if nmiss(of num1-num5) then

do;

if nmiss(num1) then

num1=sum_num;

if nmiss(num2) then

num2=sum_num;

if nmiss(num3) then

num3=sum_num;

if nmiss(num4) then

num4=sum_num;

if nmiss(num5) then

num5=sum_num;

end;

run;

Regards,

Amir.

Re: Replace dot(.) with sum value of each observation.

Keith — Mon, 08 Oct 2012 11:52:37 GMT

data want;

set test;

array nums{*} NUM: ;

_sumnum=sum(of nums{*});

do _i=1 to dim(nums);

if missing(nums{_i}) then nums{_i}=_sumnum;

end;

drop _: ;

run;

Re: Replace dot(.) with sum value of each observation.

Linlin — Mon, 08 Oct 2012 12:05:51 GMT

DATA TEST;

INPUT SUBJECT $ NUM1 NUM2 NUM3 NUM4 NUM5;

DATALINES;

A01 . 2 5 36 .

B01 65 25 54 . 3

C01 4 . . 4 5

D01 . . 5 4 5

E01 . . . 5 4

RUN;

%let n=5;

data want;

set test;

array _num num1-num&n;

sum_num=sum(of num1-num&n);

do over _num;

if _num=. then _num=sum_num;

end;

run;

proc print;run;

Obs SUBJECT NUM1 NUM2 NUM3 NUM4 NUM5 sum_num

1 A01 43 2 5 36 43 43

2 B01 65 25 54 147 3 147

3 C01 4 13 13 4 5 13

4 D01 14 14 5 4 5 14

5 E01 9 9 9 5 4 9

Re: Replace dot(.) with sum value of each observation.

Haikuo — Mon, 08 Oct 2012 13:24:39 GMT

data want;

set test;

array num num:;

sum_number=sum(of num:);

do over num;

num=coalesce(num, sum_number);

end;

run;

Haikuo

Re: Replace dot(.) with sum value of each observation.

rohit_prajapati — Tue, 09 Oct 2012 07:22:08 GMT

Wow thats great.

Thank you everyone.

i had already tried with Amir code but i wanted in shortest method but its wonderful.

Thanks everyone.

Regards,

Rohit

Re: Replace dot(.) with sum value of each observation.

rohit_prajapati — Tue, 09 Oct 2012 07:25:03 GMT

wow great Hai.Kuo.

But How COALESCE function works ? Never seen this function.

Can u pls explain?

Rohit

Re: Replace dot(.) with sum value of each observation.

Patrick — Tue, 09 Oct 2012 09:08:57 GMT

COALESCE takes as documented in the SAS Online doc the first non missing value from a list.