Hello All.
I have a dataset like below.
patient_id sd1 sd2 sd3 sd4 sd5 sd6
A1 2 . 4 . 6 .
A2 . 3 7 . . 9
I want to create a new dataset like below.
patient_id sd1 sd2 sd3 sd4 sd5 sd6
A1 2 4 6 . . .
A2 3 7 9 . . .
How can I do this?Any leads will be appreciated.
Regards,
Aninda
Hi,
Something like:
data have; p_id="A1"; sd1=2;sd2=.; sd3=4; sd4=.; sd5=6.; sd6=.; run; data want (drop=i tmp); set have; array sd sd:; tmp=tranwrd(catx(",",of sd{*}),".,",""); call missing(of sd{*}); do i=1 to countw(tmp,","); sd{i}=input(scan(tmp,i,","),best.); end; run;
Thanks RW9.
It works fine .
Regards,
Aninda
Hi,
I just made a tiny change to make it more flexible. Rather than specifying sd{6} - six elements, put array sd sd:; which means create an array of all elements with prefix sd.
@aninda_metal wrote:
Thanks RW9.
It works fine .
Regards,
Aninda
Then you can do @RW9 a favor by marking his post as the accepted solution.
Brute force attack, from a longtime procedural programmer 😉 :
data have;
input patient_id $ sd1-sd6;
cards;
A1 2 . 4 . 6 .
A2 . 3 7 . . 9
;
run;
data want;
set have;
array sd {*} sd1-sd6;
i1 = 1;
i2 = 2;
do until (i2 > dim(sd));
do while (sd{i1} ne . and i2 <= dim(sd));
i1 + 1;
i2 + 1;
end;
if i2 <= dim(sd) then x = sd{i2};
do while (i2 <= dim(sd) and x = .);
i2 + 1;
if i2 <= dim(sd) then x = sd{i2};
end;
if i2 <= dim(sd)
then do;
sd{i1} = sd{i2};
sd{i2} = .;
end;
end;
drop i1 i2 x;
run;
Yet another way:
data have;
input patient_id $ sd1-sd6;
cards;
A1 2 . 4 . 6 .
A2 . 3 7 . . 9
;
run;
data want(drop = i j);
set have;
array k[*] sd1 - sd6;
do i = 1 to dim(k);
if missing(k[i]) then
do j = i + 1 to dim(k);
if not missing(k[j]) then do;
k[i] = k[j];
k[j] = .;
leave;
end;
end;
end;
run;
data have; input patient_id $ sd1-sd6; cards; A1 2 . 4 . 6 . A2 . 3 7 . . 9 ; run; data want; set have; array x{*} sd1-sd6; array y{*} new1-new6; n=0; do i=1 to dim(x); if not missing(x{i}) then do; n+1; y{n}=x{i}; end; end; drop n i sd1-sd6; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.