BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

9 REPLIES 9
ballardw
Super User

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
Diamond | Level 26

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.

--
Paige Miller
Astounding
PROC Star

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
Quartz | Level 8

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

podarum
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

Awesome...great stuff... thanks

sam369
Obsidian | Level 7

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

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1211 views
  • 6 likes
  • 7 in conversation