DATA Step, Macro, Functions and more

Problem with finding a substring in a string when it exists

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

Problem with finding a substring in a string when it exists

Sample data attached (Sheet1).

I want to produce a column called all_nonmatch which would give me the issue numbers in column B but not column C (eg, for polnum = A, the result would be '2').

I'm open to other solutions. The one I'm using is sufficient I believe, because if I hardcode the numbers in (as character substrings), it works. However, the goal is to create a loop that would go through each issue (there could be up to 6 issues) and check for it vs the matched_issues list.

Here's my code (I was testing, I haven't converted to a loop/macros/whatever I'll need to production-ize it). I've tried a lot of different things (using macro variables instead of a new column for issue being one example). If I force the length of the 'issue' variable to 1 it works (for the one character numbers-- but these will go up to 3 characters), and if I hardcode the string '1' instead of issue as the 'excerpt' argument for INDEXW it works. This leads me to believe that I have some kind of trailing blank issue. But I also wrapped the SCAN function with every kind of stripping function that i know (STRIP, COMPRESS, COMPBL, TRIM, LEFT) to no avail. I tried converting the issue variable to a number.

My output is on the output tab. Everything ends up in the nonmatch columns, even though there should be matches.

data test2; set test1;

  issue=scan(all_issues,1,';');

  if indexw(matched_issues,issue,';')>0 then match1=issue;

  else nonmatch1=issue;

  issue=scan(all_issues,2,';');

  if indexw(matched_issues,issue,';')>0 then match2=issue;

  else nonmatch2=issue;

  issue=scan(all_issues,3,';');

  if indexw(matched_issues,issue,';')>0 then match3=issue;

  else nonmatch3=issue;

  all_nonmatch=catx(';',nonmatch1,nonmatch2,nonmatch3);er

run;++


Accepted Solutions
Solution
‎10-02-2014 11:12 AM
Super User
Posts: 11,343

Re: Problem with finding a substring in a string when it exists

Try changing to

indexw(matched_issues,strip(issue),';')>0

If you look at the size of ISSUE in the result data set, you'll likely find it is 200 characters and is using the blanks at the end for the comparison.

View solution in original post


All Replies
Solution
‎10-02-2014 11:12 AM
Super User
Posts: 11,343

Re: Problem with finding a substring in a string when it exists

Try changing to

indexw(matched_issues,strip(issue),';')>0

If you look at the size of ISSUE in the result data set, you'll likely find it is 200 characters and is using the blanks at the end for the comparison.

Frequent Contributor
Posts: 80

Re: Problem with finding a substring in a string when it exists

That works. Thanks.

But why does it only work that way? Why doesn't this work:

issue=strip(scan(XXX));

Super User
Posts: 11,343

Re: Problem with finding a substring in a string when it exists

I suspect it is because Indexw is searching for what you have specified as a "word". It is up to you to make sure that "word" is the content you actually want.

gqy
N/A
Posts: 1

Re: Problem with finding a substring in a string when it exists

hello i am new user

🔒 This topic is solved and locked.

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

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