topic Re: Unable to switch a value from one row to another row in SAS Programming

Unable to switch a value from one row to another row

aninda_metal — Fri, 05 Aug 2016 09:34:13 GMT

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

Re: Unable to switch a value from one row to another row

RW9 — Fri, 05 Aug 2016 10:14:25 GMT

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;

Re: Unable to switch a value from one row to another row

Kurt_Bremser — Fri, 05 Aug 2016 10:09:16 GMT

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;

Re: Unable to switch a value from one row to another row

aninda_metal — Fri, 05 Aug 2016 10:13:32 GMT

Thanks RW9.

It works fine .

Regards,

Aninda

Re: Unable to switch a value from one row to another row

RW9 — Fri, 05 Aug 2016 10:15:26 GMT

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.

Re: Unable to switch a value from one row to another row

KachiM — Fri, 05 Aug 2016 11:24:26 GMT

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;

Re: Unable to switch a value from one row to another row

Ksharp — Sat, 06 Aug 2016 07:34:57 GMT


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;

Re: Unable to switch a value from one row to another row

Kurt_Bremser — Mon, 08 Aug 2016 06:03:12 GMT

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