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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.