DATA Step, Macro, Functions and more

WHERE, LIKE, wildcard characters, and macro substitutions

Reply
N/A
Posts: 0

WHERE, LIKE, wildcard characters, and macro substitutions

I have a dataset with the variable "field_number" and I want to retain all records that start with a 'T' or 'B'. I am using this macro substitution and calling it in a where clause:

%let fieldnum = 'T%' or 'B%';

data step1; set data;
where Field_number like &fieldnum
run;

It works if I only want 'T%' samples but not if I also want 'B%' samples. Seems simple, but I just can't figure out the correct syntax.
Respected Advisor
Posts: 3,799

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Don't you need another like operator?

%let fieldnum = like 'T%' or like 'B%';
Respected Advisor
Posts: 3,799

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Don't you need another like operator?

%let fieldnum = like 'T%' or like 'B%';
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to data_null__
I suppose that would work but it's not the clean solution I was looking for. Tried it and received errors. Probably have incorrect parenthesis in the where clause.


Message was edited by: DDC
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Why not just:

where field_number in: ('T' 'B');
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Because the field_number is more than one character, and I want those that start with T or B (which is why I have the %). And I can't get IN to work with character wildcard.
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
The colon after the IN tells SAS to look for cases where the value starts with the characters/s in the quotes.
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Ahhh, I overlooked the colon in your post. Works now. Good to know. Thanks a lot.
N/A
Posts: 0

Re: WHERE, LIKE, wildcard characters, and macro substitutions

Posted in reply to deleted_user
Yeh, it's only little but it can save a whole lot of effort.
Ask a Question
Discussion stats
  • 8 replies
  • 734 views
  • 0 likes
  • 2 in conversation