BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cau83
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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.

cau83
Pyrite | Level 9

That works. Thanks.

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

issue=strip(scan(XXX));

ballardw
Super User

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
Calcite | Level 5 gqy
Calcite | Level 5

hello i am new user

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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