topic Re: Using Or in a case statement in proc sql in SAS in SAS Programming

Using Or in a case statement in proc sql in SAS

GN0001 — Mon, 26 Jul 2021 21:49:10 GMT

Hello team,

I need to use or statement in a case statement. It keeps giving me errors:

Proc sql;
create table mytable as;

select a.*, b* ,
case when (a.diagcode1 <150  and a.diagcode2 <12) then 'no more'
case when missing(a.diagcode) or missing(a.diacode2) then 'non existent'
else ' '
end as 'new stata'
from a Left join b...

what is the problem?

Regards

blueblue

Re: Using Or in a case statement in proc sql in SAS

SASKiwi — Mon, 26 Jul 2021 22:41:39 GMT

Try this - but please remember to post your SAS log including errors as we aren't mind readers 😉

Proc sql;
create table mytable as

select a.*, b* ,
case 
  when (a.diagcode1 <150  and a.diagcode2 <12) then 'no more'
  when missing(a.diagcode) or missing(a.diacode2) then 'non existent'
else ' '
end as new_stata
from a Left join b...

Re: Using Or in a case statement in proc sql in SAS

Reeza — Mon, 26 Jul 2021 22:11:57 GMT

1. Too many CASE (twice, should only be once) One CASE per new variable being created.
2. Too early semicolon - no semicolon after the AS on the CREATE TABLE clause
3. Order of operations. SAS puts numeric missing as the lowest values so if you first check if the values are < 12 and 150 and both are missing you'll get them coded into "no more" when it should be in non existent.

Those are for starters...there could be more.

Re: Using Or in a case statement in proc sql in SAS

Reeza — Mon, 26 Jul 2021 22:21:44 GMT

And if there are errors in your code include them in the post.

Re: Using Or in a case statement in proc sql in SAS

PGStats — Tue, 27 Jul 2021 03:44:46 GMT

Also

as 'new stata'

should be

as 'new strata'n