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;++
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.
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.
That works. Thanks.
But why does it only work that way? Why doesn't this work:
issue=strip(scan(XXX));
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.
hello i am new user
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.