topic Re: Subsetting based on multiple criteria in SAS Programming

Subsetting based on multiple criteria

Marjolein — Fri, 07 Oct 2016 15:41:39 GMT

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

Re: Subsetting based on multiple criteria

Patrick — Fri, 07 Oct 2016 16:02:34 GMT

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;

Re: Subsetting based on multiple criteria

Astounding — Fri, 07 Oct 2016 16:13:42 GMT

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.

Re: Subsetting based on multiple criteria

Marjolein — Fri, 07 Oct 2016 17:33:29 GMT

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. -##

Re: Subsetting based on multiple criteria

Marjolein — Fri, 07 Oct 2016 17:39:45 GMT

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!

Re: Subsetting based on multiple criteria

Marjolein — Mon, 10 Oct 2016 07:52:17 GMT

It worked! The variables were numeric indeed. Thanks a lot! 😄