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,777

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

Don't you need another like operator?

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

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

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

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

Why not just:

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

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

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

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

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

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