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
Amethyst | Level 16

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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