BookmarkSubscribeRSS Feed
aninda_metal
Calcite | Level 5

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
aninda_metal
Calcite | Level 5

Thanks RW9.

It works fine .

Regards,

Aninda

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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;
KachiM
Rhodochrosite | Level 12

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;
Ksharp
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 987 views
  • 5 likes
  • 5 in conversation