Help using Base SAS procedures

Filter data records using Wildcards

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Filter data records using Wildcards

[ Edited ]

Hello

I am attempting to query all records from a table where VQueue ends with .CT
Help please.

proc sql;
create table Queue as
select * 
  from hierarchy
   WHERE VQueue = '*.CT';
quit;

Accepted Solutions
Solution
‎08-19-2016 10:11 AM
SAS Super FREQ
Posts: 8,739

Re: Filter data records using Wildcards

[ Edited ]

Hi:
SAS has a lot of string processing functions (such as FIND, FINDW, INDEX, INDEXC, etc) and Perl Regular Expressions which might work for you. It also might be that a simple LIKE or CONTAINS operator might work for you.

For example, this PROC PRINT shows the use of a WHERE clause using the LIKE operator. With the LIKE operator, the underscore (_) is a wildcard for one character and the percent sign (%) is a wildcard for more than one character.

 

See "Syntax of WHERE Expression" in the SAS documentation.

cynthia

data words;
    infile datalines;
    input vqueue $;
    return;
    datalines;
CTXYZ
XYZ.CT
ABCTG
RS.CT
;
run;

proc print data=words;
    where VQueue like '%.CT';
    title 'Search for %.CT - where % represents any characters before .CT';
run;

proc print data=words;
    title 'Search for __XYZ -- 2 underscores represent any 2 characters before XYZ';
    where VQueue like '__XYZ';
run;



View solution in original post


All Replies
Solution
‎08-19-2016 10:11 AM
SAS Super FREQ
Posts: 8,739

Re: Filter data records using Wildcards

[ Edited ]

Hi:
SAS has a lot of string processing functions (such as FIND, FINDW, INDEX, INDEXC, etc) and Perl Regular Expressions which might work for you. It also might be that a simple LIKE or CONTAINS operator might work for you.

For example, this PROC PRINT shows the use of a WHERE clause using the LIKE operator. With the LIKE operator, the underscore (_) is a wildcard for one character and the percent sign (%) is a wildcard for more than one character.

 

See "Syntax of WHERE Expression" in the SAS documentation.

cynthia

data words;
    infile datalines;
    input vqueue $;
    return;
    datalines;
CTXYZ
XYZ.CT
ABCTG
RS.CT
;
run;

proc print data=words;
    where VQueue like '%.CT';
    title 'Search for %.CT - where % represents any characters before .CT';
run;

proc print data=words;
    title 'Search for __XYZ -- 2 underscores represent any 2 characters before XYZ';
    where VQueue like '__XYZ';
run;



Contributor
Posts: 73

Re: Wildcards

Thank you, i went with the Contains and it worked out.

Fred
PROC Star
Posts: 7,356

Re: Wildcards

Fred,

Since you had indicated that you wanted strings that "ended with "CT", I would have gone with Cynthia's suggestion to use the LIKE operator.

Art
New Contributor
Posts: 4

Re: Wildcards

How can we use Wild Characters in Data Step?

I am trying to get something like.

data Apple;

Set Ball;

where upcase(trim(Column2)) like '%CAR%';

run;

But i got an error for this.

Is there any way to use this in Datastep?

Super User
Posts: 9,662

Re: Wildcards

data Apple;

Set Ball;

if find(upcase(trim(Column2)) , 'CAR' ) ;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 27303 views
  • 0 likes
  • 5 in conversation