Unable to switch a value from one row to another row

[aninda_metal]

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

[RW9]

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]

Thanks RW9.

It works fine .

Regards,

Aninda

[RW9]

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]

---

@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.

[Kurt_Bremser]

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]

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]

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;