topic Re: Use WHERE statement for multiple variables in SAS Programming

Use WHERE statement for multiple variables

westbestern — Tue, 26 Oct 2021 18:09:43 GMT

I am trying to create a new dataset from an existing one where I only keep certain coded variables. For example, I only want people who are HIV negative AND have engaged in condomless sex or heroin use or sex work, etc.

Here is what I have so far:

data hivneg;
set olddata;
where HIV1 = 2; run;



data hivneg;
where SexBehav3a<=1 or SexBehav4a<=1 or heroin_use=1;
run;

Why doesn't this work? What is the best way to code this?

Re: Use WHERE statement for multiple variables

Reeza — Tue, 26 Oct 2021 18:12:29 GMT

I think you're missing the SET statement and also suspect one of those ORs should be an AND.

data hivneg;
SET oldData;
where (SexBehav3a<=1 or SexBehav4a<=1) AND heroin_use=1;
run;

@westbestern wrote:

I am trying to create a new dataset from an existing one where I only keep certain coded variables. For example, I only want people who are HIV negative AND have engaged in condomless sex or heroin use or sex work, etc.

Here is what I have so far:
data hivneg;
set olddata;
where HIV1 = 2; run;



data hivneg;
where SexBehav3a<=1 or SexBehav4a<=1 or heroin_use=1;
run;
Why doesn't this work? What is the best way to code this?

Re: Use WHERE statement for multiple variables

westbestern — Tue, 26 Oct 2021 18:19:36 GMT

data hivneg;
set olddata;
where (HIV1=2) AND (SexBehav3a=<1 or SexBehav4a=<1 or SexBehav5a=<1);
run;

I tried doing this but it does not work. It says there's a syntax error. Are you able to put multiple "or" statements in that line?

Re: Use WHERE statement for multiple variables

Reeza — Tue, 26 Oct 2021 18:24:31 GMT

Show the actual error please, it usually has enough information to debug the issue.

Re: Use WHERE statement for multiple variables

westbestern — Tue, 26 Oct 2021 18:27:17 GMT

Re: Use WHERE statement for multiple variables

Quentin — Tue, 26 Oct 2021 18:32:06 GMT

Yes, multiple ORs are allowed.

In the code you posted:

where (HIV1=2) AND (SexBehav3a=<1 or SexBehav4a=<1 or SexBehav5a=<1);

You're using =< but you intend <=

where (HIV1=2) AND (SexBehav3a<=1 or SexBehav4a<=1 or SexBehav5a<=1);

If that doesn't work, please post the log, as @Reeza mentioned.

Also note that SAS uses binary logic (true/false) rather than trinary (true/false/null), and a missing value sorts as a low value. So SexBehav3a<=1 will be true if SexBehav3a has a missing value.

Re: Use WHERE statement for multiple variables

westbestern — Tue, 26 Oct 2021 18:38:05 GMT

That was it! Thank you!

Re: Use WHERE statement for multiple variables

Reeza — Tue, 26 Oct 2021 18:44:28 GMT

Interestingly not in your first post though....

Re: Use WHERE statement for multiple variables

ballardw — Tue, 26 Oct 2021 21:06:05 GMT

Which is why I tend to use the mnemonic LE and GE for most of these comparisons. Then I don't get the order wrong.