Help using Base SAS procedures

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

Reply
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 -

SUBJECTNUM1NUM2NUM3NUM4NUM5SUM_NUM
A014325364343
B016525541473147
C01413134513
D01141454514
E01999549

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

Super Contributor
Posts: 282

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

Respected Advisor
Posts: 3,156

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 numSmiley Happy;

       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

Respected Advisor
Posts: 4,173

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

Ask a Question
Discussion stats
  • 7 replies
  • 362 views
  • 3 likes
  • 6 in conversation