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

Occasional Contributor
Posts: 17

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

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.

Regards,

Rohit

Super Contributor
Posts: 339

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

Posted in reply to rohit_prajapati

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.

Regular Contributor
Posts: 151

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

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;

Super Contributor
Posts: 1,636

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

Posted in reply to rohit_prajapati

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

Posts: 3,167

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

Posted in reply to rohit_prajapati

data want;

set test;

array num num:;

sum_number=sum(of num;

do over num;

num=coalesce(num, sum_number);

end;

run;

Haikuo

Occasional Contributor
Posts: 17

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

wow great Hai.Kuo.

But How COALESCE function works ? Never seen this function.

Can u pls explain?

Rohit

Posts: 4,736

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

Posted in reply to rohit_prajapati

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

Occasional Contributor
Posts: 17

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

Posted in reply to rohit_prajapati

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

Discussion stats
• 7 replies
• 397 views
• 3 likes
• 6 in conversation