BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ihsan-Mahdi
Quartz | Level 8

Hello,

I would like to limit my data set to a specific list of county names. Is there a quicker way than repeated IF statements?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

 

When you want to compare a single variable to a list of values then the IN operator is your tool of choice:

 

   countyname in ('Thisname' 'Thatname' 'Another name');

 

Subsetting the data could be done with either a Where statement or dataset option or an IF statement:

Data want;
    set have;
   where countyname not in ('Thisname' 'Thatname' 'Another name');
run;

or

Data want;
    set have;
   if countyname not in ('Thisname' 'Thatname' 'Another name');
run;

If the list you want to keep is shorter then don't use the NOT in the above examples.

The IN operator for character values is equivalent to a bunch of equals, so if you have case differences such as your values with "Name"  "name" "nAme" etc, for spelling you will need to address that somehow.

 

 

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

Yes. Please specify your problem more carefully and you'll have a usable code answer in no time.

Ihsan-Mahdi
Quartz | Level 8

So, I'm working with a data set of about 1M records. A variable in the set specifies the county name. I would like to keep only the records in which the county name belongs to a specific list of 39 counties and delete the rest.

I usually use IF statements, IF county_name="so and so" THEN delete. But since there is a long list of count names I wish to delete I was hoping there would be a shorter way.

 

Thank you!

ballardw
Super User

 

When you want to compare a single variable to a list of values then the IN operator is your tool of choice:

 

   countyname in ('Thisname' 'Thatname' 'Another name');

 

Subsetting the data could be done with either a Where statement or dataset option or an IF statement:

Data want;
    set have;
   where countyname not in ('Thisname' 'Thatname' 'Another name');
run;

or

Data want;
    set have;
   if countyname not in ('Thisname' 'Thatname' 'Another name');
run;

If the list you want to keep is shorter then don't use the NOT in the above examples.

The IN operator for character values is equivalent to a bunch of equals, so if you have case differences such as your values with "Name"  "name" "nAme" etc, for spelling you will need to address that somehow.

 

 

Ihsan-Mahdi
Quartz | Level 8
Thank you so much!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1510 views
  • 1 like
  • 3 in conversation