create 2 new vars a new and old from 2 rows

[podarum]

Hi,  I'm trying to create 2 new vars from 2 rows (eg. and old and a new) ... thanks

HAVE:

LOC   ID     PL    Other

342    3       A        1d

342    3       A         2f

456    5       B         e4

456    6       B         d2

645    7       C         r5

645    7       D         e7

WANT:

LOC     old_ID     new_ID       old_PL      new_PL       Other

342          3              3                   A              A                  1d

456          5              6                   B              B                  e4

645          7              7                   C              D                  r5

Keeping the Other variable is not that important, it can be dropped if it complicates the code... Thanks

[ballardw]

Assuming LOC always has only two values

Data want (keep=loc old_id new_id old_pl new_pl other);

     set have;

     by LOC unsorted;

     old_id=lag (id);

     new_id=id;

     old_pl = lag (pl);

     new_PL = pl;

     other = lag(other);

     if last.loc;

run;

may do what you need.

[PaigeMiller]

Assign a variable to the original data set that determines if the row number is odd or even (actually, this produces 0 for even and 1 for odd)

odd_Even=mod(_n_,2);

Then create a new data set by merging the odd rows with the even rows and assign the values to old_id, new_id old_Pl new_PL.

The key part is the merge statement

merge old_dataset(where=(odd_even=0) rename=(id=old_id pl=old_pl)) old_dataset(where=(odd_even=1) rename=(id=new_id pl=new_pl) drop=other);

by loc;

This will work if each LOC has exactly 2 rows. Otherwise, there are modifications you would need to the dataset and program if sometimes LOC can have fewer than or greater than 2 rows.

[Astounding]

Here's a variation that handles "Other" and gives you a fighting chance if you have 1 (or even 3) records per LOC:

data want;

   do i=1 to 2 until (last.LOC);

      set have (rename=(other=original_other));

      by LOC;
      if i=1 then do;

        old_ID=ID; old_PL=PL; other=original_other;

      end;

      else do;

         new_ID=ID; new_PL=PL;

      end;

   end;

   drop ID PL original_other;

run;

Good luck.

[podarum]

Thanks for all the great answers.. I appreciate all the input and help.

[podarum]

How could I not include rows if it is only a stand alone and has no pairing..  for example

HAVE

LOC   ID     PL    Other

342    3       A        1d

342    3       A         2f

399    2       F         9d

456    5       B         e4

456    6       B         d2

645    7       C         r5

645    7       D         e7

738    5       E          8s

WANT:

LOC     old_ID     new_ID       old_PL      new_PL       Other

342          3              3                   A              A                  1d

456          5              6                   B              B                  e4

645          7              7                   C              D                  r5

Thanks again..

[NickR]

Below code should give your desired output.

data want;

  set have(rename=(other=other_old));

  by loc;

  if first.loc then seq=.;

  seq+1;

  retain old_ID new_ID old_PL new_PL Other;

  if seq=1 then do; old_id=id; old_pl=pl; other=other_old; end;

  if seq=2 then do; new_id=id; new_pl=pl; output; end;

  drop id pl other_old seq;

run;

[podarum]

Awesome...great stuff... thanks

[sam369]

Here the sql apporach

data test;

set test;

by loc notsorted;

if first.loc then var=0;

else var=1;

run;

proc sql;

select a.loc,a.old_id,a.old_pl,a.old_other,b.new_id,b.new_pl,b.new_other

  from test(rename=(id=old_id pl=old_pl other=old_other)) as a   full join

       test(rename=(id=new_id pl=new_pl other=new_other)) as b

         on a.loc=b.loc

         having a.var=0 & b.var=1;

;

quit;

Regards

Sam

[joehinson]

...now a hash solution:

data _null_;

     length old_ID new_ID 8 old_PL new_PL $4;

     if(1=2) then set have;

     declare hash w (ordered:"a");

     rc=w.defineKey("LOC");

     rc=w.defineData("LOC","old_ID","new_ID","old_PL","new_PL","Other");

     rc=w.defineDone();

     call missing(of _all_);

     x=0;

     do until(done);

          x=x+1;

          y=band(1,x);

          set have end=done;

          if y=1 then do;

               OD=ID;

               OP=PL;

               OTH=Other;

          end;

          else if y=0 then do;

               ND=ID;

               NP=PL;

          end;

          rcw=w.replace(key:LOC,data:LOC,data:OD,data:ND,data:OP,data:NP,data:OTH);

     end;

     w.output(dataset:"want");

     stop;

run;