DATA Step, Macro, Functions and more

Looking back to get records...

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Looking back to get records...

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

 

 

 

 

 

 

 

 

  


Accepted Solutions
Solution
‎04-05-2018 03:02 PM
Super User
Posts: 6,543

Re: Looking back to get records...

Posted in reply to Aidaan_10

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

View solution in original post


All Replies
Super Contributor
Posts: 478

Re: Looking back to get records...

Posted in reply to Aidaan_10

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 14

Re: Looking back to get records...

Posted in reply to SuryaKiran

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 

Solution
‎04-05-2018 03:02 PM
Super User
Posts: 6,543

Re: Looking back to get records...

Posted in reply to Aidaan_10

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

Occasional Contributor
Posts: 14

Re: Looking back to get records...

Posted in reply to Astounding

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

PROC Star
Posts: 1,351

Re: Looking back to get records...

[ Edited ]
Posted in reply to Aidaan_10
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 151 views
  • 0 likes
  • 4 in conversation