topic Re: Looking back to get records... in SAS Programming

Looking back to get records...

Aidaan_10 — Thu, 05 Apr 2018 15:26:12 GMT

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

Re: Looking back to get records...

SuryaKiran — Thu, 05 Apr 2018 15:49:21 GMT

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

Re: Looking back to get records...

Aidaan_10 — Thu, 05 Apr 2018 15:56:22 GMT

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

Re: Looking back to get records...

Astounding — Thu, 05 Apr 2018 18:13:15 GMT

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;

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

Re: Looking back to get records...

Aidaan_10 — Thu, 05 Apr 2018 19:01:53 GMT

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

Re: Looking back to get records...

novinosrin — Thu, 05 Apr 2018 19:40:28 GMT

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;