I have a large data set with a column called ‘identifier.’ About 90% of cases include a 7 digit ID in the identifier column. For the remaining 10% of cases, however, there wasn’t a 7 digit ID available, so the person who created the data set made up her own faux ID by concatenating address and zip code. I need to split out the “true” IDs so I can use them to merge with another file. I want to leave the ‘identifier’ column as is, since the faux IDs may be helpful in some circumstances, but create a new numeric column called ID that only includes the 7 digit ID numbers (and leaves the rest blank). I’m very new to SAS, so I’m sure there’s a simple solution but I just don’t know it! I’ve tried using trim and subst commands but I just can’t get this to work.
Here's an example of the data I have to work with (the final column is the result I’m looking for):
Identifier address zip ID
1234567 240 Main Ave. 44230 1234567
7654321 800 Elm St. 43240 7654321
211980thSt43215 22119 80th St. 43215 .
Thanks in advance for any help you can give.
Susan
if length(Identifier )=7 then id=Identifier ;
or safe bet-->
if length(identifier)=7 and anyalpha(identifier)=0 then id=identifier;
data want;
set have;
if length(identifier)=7 and anyalpha(identifier)=0 then id=identifier;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.