topic Re: WHERE, LIKE, wildcard characters, and macro substitutions in SAS Programming

WHERE, LIKE, wildcard characters, and macro substitutions

deleted_user — Tue, 24 Mar 2009 15:39:45 GMT

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.

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

data_null__ — Tue, 24 Mar 2009 15:42:31 GMT

Don't you need another like operator?

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

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

data_null__ — Tue, 24 Mar 2009 15:42:44 GMT

Don't you need another like operator?

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

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

deleted_user — Tue, 24 Mar 2009 15:45:36 GMT

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

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

deleted_user — Tue, 24 Mar 2009 15:57:08 GMT

Why not just:

where field_number in: ('T' 'B');

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

deleted_user — Tue, 24 Mar 2009 16:05:10 GMT

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.

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

deleted_user — Tue, 24 Mar 2009 16:07:38 GMT

The colon after the IN tells SAS to look for cases where the value starts with the characters/s in the quotes.

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

deleted_user — Tue, 24 Mar 2009 16:11:21 GMT

Ahhh, I overlooked the colon in your post. Works now. Good to know. Thanks a lot.

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

deleted_user — Tue, 24 Mar 2009 16:17:03 GMT

Yeh, it's only little but it can save a whole lot of effort.