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

I would like extract the data like below. But it is creating table with all rows from maps.uscity instead of required records.

It looks I need to tweak the code in like operator to get only the records that has city like below.

proc sql;

create table FI_Locations as select * from maps.uscity where city like  '%Atlanta%' or '%Cary%' or '%Hartford%' ;

quit;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

LIKE only can handle one expression one time.

proc sql;

create table FI_Locations as select * from maps.uscity where city like  '%Atlanta%' or  city like '%Cary%' or  city like  '%Hartford%' ;

quit;

Xia Keshan

View solution in original post

5 REPLIES 5
Babloo
Rhodochrosite | Level 12

any thoughts?

JuanS_OCS
Azurite | Level 17

Hi Babloo,

depending on your database, maybe you can use the "contains" comparator, instead of  "like", then you won;t need the wildcards.

Babloo
Rhodochrosite | Level 12

I would like to know if i can combine the below data step into one step.

proc sql;

create table FI_Locations_at as select * from maps.uscity where city contains  'Atlanta';

quit;

proc sql;

create table FI_Locations_ca as select * from maps.uscity where city contains  'Cary';

quit;

Ksharp
Super User

LIKE only can handle one expression one time.

proc sql;

create table FI_Locations as select * from maps.uscity where city like  '%Atlanta%' or  city like '%Cary%' or  city like  '%Hartford%' ;

quit;

Xia Keshan

user24feb
Barite | Level 11

.. if you need to combine your where-statement with and-statements you might prefer ..

proc sql;

create table FI_Locations as select * from maps.uscity where

  (Case When city like '%Atlanta%' or

             city like '%Cary%' or

             city like '%Hartford%' Then 1

             Else 0

   End)=1;

quit;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 36086 views
  • 1 like
  • 4 in conversation