## Scan Function for missing values

Solved
Frequent Contributor
Posts: 102

# Scan Function for missing values

Hello SAS guys,

I have data like below, the want data did not  give me  second response empty of id 123 and sixth response empty of id 456. How do I get the right answer? Thanks a lot!

data have ;
length id \$3 response \$15 ;
input id response ;
datalines ;
123 A||B|C|D|A|C
456 A|A|C|D|B||C
;
run ;

data want;
set have;
cnt=countw(response,'|');
i=1;
do while (i<cnt);
resp=scan(response,i,'|');
output;
i=i+1;
end;
drop response;
rename resp=response;
run;

the output is

123 A

123 B

123 C

123 D

123 A

456 A

456 A

456 C

456 D

456 B

But I need :

123  A

123

123 B

123 C

.....

Accepted Solutions
Solution
‎10-18-2017 12:12 PM
Super User
Posts: 6,928

## Re: Scan Function for missing values

Normally (as you can see), SCAN treats consecutive delimiters as one large delimiter.  If you want them each to mark a separate word, you can tell SCAN to do that:

resp=scan(response,i,'|', 'M') ;

All Replies
Solution
‎10-18-2017 12:12 PM
Super User
Posts: 6,928

## Re: Scan Function for missing values

Normally (as you can see), SCAN treats consecutive delimiters as one large delimiter.  If you want them each to mark a separate word, you can tell SCAN to do that:

resp=scan(response,i,'|', 'M') ;

Frequent Contributor
Posts: 102

## Re: Scan Function for missing values

Even I put resp=scan(response,i,'|', 'M') ;

the results are

id     cnt    i     response

123  6       1   A

123 6        2

123 6       3   B

123 6        4  C

123 6       5    D

456 6       1  A

456  6      2  A

456  6      3  C

456  6      4  D

456   6      5  B

it is not the result which match with the original data

Frequent Contributor
Posts: 102

## Re: Scan Function for missing values

Thank you very much Astounding. I adjusted my code and your help works

Super User
Posts: 9,829

## Re: Scan Function for missing values

Pretty sure I covered this in your other post, but:

```data have;
length id \$3 response \$15;
input id response;
datalines;
123 A||B|C|D|A|C
456 A|A|C|D|B||C
;
run;

data want (drop=i);
set have;
do i=1 to lengthn(response);
if char(response,i) ne "|" then result=char(response,i);
else do;
output;
result="";
end;
end;
run;
```
☑ This topic is solved.