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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.