BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Krysia24
Obsidian | Level 7

What is the best way to concatenate the following: I am trying to create a unique identifier to link two datasets. I am exploring the option of using SSN and DOB {not all observations have a valid SSN recorded; hence why I am adding DOB. DOB is currently in the MMDDYY10. format. SSN is currently in character format.

 

What is the best way to concatenate these two variables into a unique identifier, eliminating blanks and without distorting the date information?

Also, how does this handle missing data (say if a DOB is missing)?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

And to remove the forward '/' 

 

data test;
ssn='123456789';
dob='02jan1993'd;
if not missing(dob) then new_var=cats(ssn,compress(put(dob,mmddyy10. -l),'/'));
else new_var=ssn;
put new_var=;
run;

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

are you after

 

 if not missing(dob) then new_var=cats(ssn,put(dob,mmddyy10. -l));

else new_var=ssn;

 

Krysia24
Obsidian | Level 7

Hi there... so that code did not do anything for the unique id (it simply outputted the ssn without the dob included).

 

Ideally if someone's SSN is 123456789 and their DOB is 01/02/1993 - then the uniqueID would be: 12345678901021993.

 

Does that make sense?

Reeza
Super User

Can you post the exact code you used please and some sample records. Since you don't provide sample data, the solutions are untested, of course.

novinosrin
Tourmaline | Level 20

Yes, please provide us a sample of your data, your output, the code you used and your log,

here is the test with my assumptions using your narrative:

 

122 data test;
123 ssn='123456789';
124 dob='02jan1993'd;
125 if not missing(dob) then new_var=cats(ssn,put(dob,mmddyy10. -l));
126 else new_var=ssn;
127 put new_var=;
128 run;

new_var=12345678901/02/1993
NOTE: The data set WORK.TEST has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

 

novinosrin
Tourmaline | Level 20

And to remove the forward '/' 

 

data test;
ssn='123456789';
dob='02jan1993'd;
if not missing(dob) then new_var=cats(ssn,compress(put(dob,mmddyy10. -l),'/'));
else new_var=ssn;
put new_var=;
run;
Reeza
Super User

@novinosrin mmddyy10 -> 10 implies dashes, 8 would not include dashes, so try mmddyy8. instead perhaps?

novinosrin
Tourmaline | Level 20

@Reeza Love your attention to detail.. Caffeine effect after 6 cups of large pike at Starbucks on student discount 🙂  Thank you though

Krysia24
Obsidian | Level 7

Looks like a had a slight typo when I first tried it. This worked for me!

ballardw
Super User

One thought to consider: You might want to include something like an underscore between the ssn portion and the date portion.

123456789_01021993

might be easier to identify the date part when humans need to fix something like a short SSN. Also if the SSN is missing then a value like

_11021993 tells you right away the SSN is completely missing where

11011993 could be confused with an incomplete SSN and a missing DOB.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1247 views
  • 2 likes
  • 4 in conversation