In the attached file you will see a variable form which I need to be able to create three datasets
1st dataset(Missing) : find values that contain missing values in any of the 23 characters of the variable SP440
2nd dataset(Unknown) find values that contain 8 in the 1st character or 9 in any of the total 23 characters
3rd dataset (Invalid entries): find values that contain any value other than P,Y, N or 8 in the 1st character
Please advise how would I write a code to create these three datasets
Regards
data missing unknown invalid;
set have;
if anyspace(SP440) then output missing;
else if first(SP440)='8' or index(SP440,'9')>0 then output unknown;
else if first(SP440) not in ('P','Y','N','8') then output invalid;
run;
Here's another way to do it. Note I create another dataset "other" which contains observations that would not go into the other 3 datasets.
data input ;
input sp440 $23. ;
cards;
Y03270745270816Y270858Y
Y03180759180957Y181033Y
Y04090027090253Y090314Y
Y04100841100926Y101003Y
Y04170832170903Y170931Y
P 172043Y172100Y
P 232235Y232256Y
P 262212Y262244Y
P 282020Y282046Y
Y04292040292109Y292125Y
Y04300524300559Y300638Y
Y04292358300122Y300139Y
Y05131507131627Y131745Y
Y05160528160600Y160619Y
Z05151244151410N N
I05221247221314Y221332Y
Y05201954202145Y202204Y
Y05211103211138Y211156Y
Y06051414051446Y051520Y
Y06082303090126Y090142Y
run ;
data missing unknown invalid other;
set input ;
if substr(sp440,1,1) not in ("P","Y","N","8") then
output invalid ;
else if substr(sp440,1,1) = "8" or find(sp440,"9") then
output unknown ;
else if find(sp440," ") then
output missing ;
else
output other ;
run ;
Hi @AMSAS if you are using an extract function but extracting no more than 1 byte of length
if substr(sp440,1,1)
can be replaced by
if char(sp440,1)
Imho, substr is at best when length to extract exceeds 1 to n bytes
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.