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
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.
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;
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
data want;
set test;
array num num:;
sum_number=sum(of num:);
do over num;
num=coalesce(num, sum_number);
end;
run;
Haikuo
wow great Hai.Kuo.
But How COALESCE function works ? Never seen this function.
Can u pls explain?
Rohit
COALESCE takes as documented in the SAS Online doc the first non missing value from a list.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.