Subsetting based on multiple criteria

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Subsetting based on multiple criteria

Hi all,

 

I have a large data-set which I want to reduce to a smaller data-set, by excluding certain participants based on either of 3 criteria (for each criterium there is a yes/no variable). If a participant answers yes to either one of the three criteria, I want that specific participant out of the data-set.

 

I used the following procedure (sorry for the typing, I do not have the syntax available on my home computer. Working with SAS 9.4):

 

DATA dataset_new;

SET dataset_old;

IF variable="yes" then DELETE;

RUN;

 

However, I got errors and as you can see this is only one criterium, not three. Am I using the right statement and how could I incorporate the criterium 'either one of the criteria'?

 

Sorry if this is beginner-level, I'm learning!

 

Kind regards and many thanks,

Marjolein

 

 


Accepted Solutions
Solution
‎10-10-2016 03:51 AM
Contributor
Posts: 23

Re: Subsetting based on multiple criteria

Ahhhh. That may have gone wrong. I indeed applied a format to the data, so I guess it is numeric...

Will try it once I'm behind my computer at uni. Really helpful. Thanks a lot!

View solution in original post


All Replies
Respected Advisor
Posts: 4,132

Re: Subsetting based on multiple criteria

Here some coding options. Should your dataset really be large (=millions of rows) then using the where clause is better as it sub-sets the data while reading and not only while writing.

You should also use a where clause if your "dataset_old" is stored in a database as the where condition will get pushed to the database for execution (and so the rows don't get first transfered to SAS only to drop them there).

DATA dataset_new;
  SET dataset_old;
  IF var1="yes" or var2="yes" or var3="yes" then DELETE;
RUN;

DATA dataset_new;
  SET dataset_old;
  IF var1 ne "yes" and var2 ne "yes" and var3 ne "yes";
RUN;

DATA dataset_new;
  SET dataset_old;
  where var1 ne "yes" and var2 ne "yes" and var3 ne "yes";
RUN;

DATA dataset_new;
  SET dataset_old (where=(var1 ne "yes" and var2 ne "yes" and var3 ne "yes"));
RUN;

Contributor
Posts: 23

Re: Subsetting based on multiple criteria

Hi Patrick,

Thanks a lot for your very quick response. Looks good. Will apply it as
soon as I'm behind my computer at uni and if it works out I will let you
know!

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Posts: 5,362

Re: Subsetting based on multiple criteria

There's nothing wrong with what you have done.  While you can combine conditions into a single statement, you don't have to (at least not for the problem you described).  In theory, it would be perfectly fine to code:

 

if varaible1='yes' then delete;

if variable2='yes' then delete;

if variable3='yes' then delete;

 

If you are getting an error, the first thing to check is whether you spelled the name of the variable correctly.  You could run a PROC CONTENTS on your data set to confirm the variable names.  It would also help track down the second possible source of an error.  Perhaps your variable is really numeric, but prints out as "yes" or "no" because it has a format applied to it.  So by running PROC CONTENTS you can see the true definition of your variable to get a better idea of what it contains.  If this turns out to be an issue, you could also run this test:

 

proc print data=mydata (obs=50);

var variable;

format variable;

run;

 

That will print a sampling of the data values, showing you what is actually contained rather than the formatted value.

Solution
‎10-10-2016 03:51 AM
Contributor
Posts: 23

Re: Subsetting based on multiple criteria

Ahhhh. That may have gone wrong. I indeed applied a format to the data, so I guess it is numeric...

Will try it once I'm behind my computer at uni. Really helpful. Thanks a lot!

Contributor
Posts: 23

Re: Subsetting based on multiple criteria

It worked! The variables were numeric indeed. Thanks a lot! Smiley Very Happy

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 387 views
  • 4 likes
  • 3 in conversation