Looking back to get records...

[Aidaan_10]

Hi All,

              I have a dataset with ORIGID variable which was the Subject original number so they change their ID to SUBJID variable in the given dataset below.

subjid       origid    rescrn

1                .             N

2                1            Y

3                 .            N

4               2             Y

5               .              N

6               .              N

7              5              Y

8              4              Y

Now if the rescrn variable value is 'Y' , I have to look back to get the original subjectid so the data I would expect would be like, the one in the New_ID variable is what I expect to have. I have to apply this logic on lot of records so how do I do that?? Any help will be greatly appreciated. Thanks 

subjid       origid    rescrn          New_ID

1                .             N                  .

2                1            Y                  1

3                 .            N                  .

4               2             Y                  1

5               .              N                  .

6               .              N                  .

7              5              Y                  5

8              4              Y                  1

[SuryaKiran]

What is your logic here? Are you missing something, can you explain more on the logic you want to apply.

[Aidaan_10]

The logic is looking back to get the original subject ID wherever the rescrn variable value is Y. So in the case of subjid with a value of 2 the original ID was 1, so in the new ID variable I want to see the value of 1 then again subject 4 is changing to 2 which indeed is changing to 1 so my value in Newid would be 1 and then subject 8 is changing to 4 which is changing to 2 which in turn is changing to value of 1 so again my NEWID variable for this subject 8 should be 1. This is expected.Thanks 

[Astounding · Accepted solution]

To make life a little easier, I'm going to assume these are character variables.  If they're actually numeric, the same approach will work but requires a couple of complicating tweaks:

data format_me;

set have;

where rescrn='Y';

start = subjid;

label = origid;

fmtname = '$trail';

run;

proc format cntlin=format_me;

run;

That creates a format that translates from SUBJID into ORIGID.  Then use the format:

data want;

set have;

if rescrn='Y' then do;

   new_id = put(subjid, $trail.);

   do k=1 to 50 until (new_id = put(new_id, $trail.));

      new_id = put(new_id, $trail.);

   end;

end;

drop k;

run;

The DO loop refuses to iterate more than 50 times per observation, just in case the ORIGID assignments form an infinite loop.  ("1" translates into "2", and "2" translates into "1" for example.)

[Aidaan_10]

Thanks a ton I guess it worked I checked that logic on my data for some subjects...thanks again

[novinosrin]

data have;
input subjid       origid    rescrn $;
datalines;
1                .             N
2                1            Y
3                 .            N
4               2             Y
5               .              N
6               .              N
7              5              Y
8              4              Y
;


data want;
if _n_=1 then do;
if 0 then set have;
if 0 then set have(rename=(origid=_origid));
   dcl hash H (dataset:'have(rename=(origid=_origid))') ;
   h.definekey  ("subjid") ;
   h.definedata('_origid');
   h.definedone () ;
end;
set have;
__origid=origid;
if __origid then do;
do while(h.find(key:__origid)=0);
if   _origid then New_ID=_origid;
else New_ID=__origid;
__origid=_origid;
end;
end;
drop _:;
run;