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

[rohit_prajapati]

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

[Amir]

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.

[Keith]

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;

[Linlin]

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

[Haikuo]

data want;

  set test;

   array num num:;

       sum_number=sum(of num:);

       do over num;

         num=coalesce(num, sum_number);

        end;

run;

Haikuo

[rohit_prajapati]

wow great Hai.Kuo.

But How COALESCE function works ? Never seen this function.

Can u pls explain?

Rohit

[Patrick]

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

[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