Continue a sequential code from a merged data table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Continue a sequential code from a merged data table

Hello everyone, 

 

I currently have a dataset where we assign a sequential number as the hidden ID to protect our pariticipants data when sharing information. Some participants have been assigned a hidden ID (sequential) in previous years and I can just the file updated with this already existing hidden ID. This dataset used to be housed as a SQLserver data table where the Identity column could easily but updated with new participants each year. I am turning into a SAS data file where I will be continuously adding new participants each year we receive new data. Is there any code in SAS that would allow me to assign the participants their already existing "hidden ID"  as well as continue the sequence with newly added participants. 

 

this is the code i am currently using however i know that the code for those that are completely brand new is not correct to continue the sequence. individualID is the actual ID they are assigned and individual code is the hidden ID i need to assign as a sequential number for new participants.

 

 

data individualhidingcodes(keep=individualid individualcode) ;
merge individualhidden(in=a) individuals2018(in=b);
by individualid;
if a and b then individualcode=individualcode;
if b and not a then individualcode=individualcode+1;
run;

this is a sample of how the current hidden code table looks:

IndividualID       Individualcode

hg111                    746589

hg112                    746590

hg113                    746591

...

 

This table i am attempting to merge is like so:

IndividualID 

hg129

hg111  *\ these that are the same in both tables would be assigned the same individual code from above*\

hg113

hg178  *\ the new IDs that have never been assigned a hidden code in previous years would need to continue from where the sequence left off in the other table *\

 

Thank you for all your help with this!

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,931

Re: Continue a sequential code from a merged data table

It sounds like you just need to find the max value currently assigned.  For example:

 

proc sql;

select max(individualcode) into : prior_max from individualhidden;

quit;

 

This should give you a macro variable that you can use in the MERGE step:

 

data individualhidingcodes (keep=individualid individualcode);

merge individualhidden (in=a) individuals2018 (in=b);

by individualid;

retain next_id &prior_max;

if a=0 then do;

   next_id + 1;

   individualcode = next_id;

end;

run;

 

It's untested code, but I think it embodies the right idea.

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 6,931

Re: Continue a sequential code from a merged data table

It sounds like you just need to find the max value currently assigned.  For example:

 

proc sql;

select max(individualcode) into : prior_max from individualhidden;

quit;

 

This should give you a macro variable that you can use in the MERGE step:

 

data individualhidingcodes (keep=individualid individualcode);

merge individualhidden (in=a) individuals2018 (in=b);

by individualid;

retain next_id &prior_max;

if a=0 then do;

   next_id + 1;

   individualcode = next_id;

end;

run;

 

It's untested code, but I think it embodies the right idea.

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 68 views
  • 1 like
  • 2 in conversation