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

Problem with finding a substring in a string when it exists

cau83 — Thu, 02 Oct 2014 14:44:32 GMT

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;++

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

ballardw — Thu, 02 Oct 2014 15:12:00 GMT

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.

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

cau83 — Thu, 02 Oct 2014 15:17:10 GMT

That works. Thanks.

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

issue=strip(scan(XXX));

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

ballardw — Thu, 02 Oct 2014 18:02:27 GMT

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.

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

gqy — Sun, 12 Oct 2014 13:55:53 GMT

hello i am new user