DATA Step, Macro, Functions and more

Scan Function for missing values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

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,537

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

View solution in original post


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

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

Posted in reply to Astounding

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

Posted in reply to Astounding

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

Super User
Super User
Posts: 9,211

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 205 views
  • 0 likes
  • 3 in conversation