topic Re: Scan Function for missing values in SAS Programming

Scan Function for missing values

daisy6 — Wed, 18 Oct 2017 15:54:11 GMT

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 C

456 D

456 B

But I need :

123 A

123

123 B

123 C

.....

Re: Scan Function for missing values

Astounding — Wed, 18 Oct 2017 16:02:12 GMT

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') ;

Re: Scan Function for missing values

daisy6 — Wed, 18 Oct 2017 16:09:49 GMT

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

Re: Scan Function for missing values

daisy6 — Wed, 18 Oct 2017 16:13:09 GMT

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

Re: Scan Function for missing values

RW9 — Wed, 18 Oct 2017 16:17:45 GMT

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;