topic Re: Proc SQL CASE statement gives errors in New SAS User

Proc SQL CASE statement gives errors

MelissaN — Wed, 15 May 2019 15:39:33 GMT

I 'm trying to do this CASE statement

Case when LN_REGULAT.ACTION_TAKEN in ( 3, 7) then LN_REGULAT.ACTION_DATE else Null end 'DENIED_DATE' ,

70 Case when LN_REGULAT.ACTION_TAKEN in( 2, 4,5,8) then LN_REGULAT.ACTION_DATE else Null end 'WITHDRAWN_DATE'

, but I got an error message saying that "ERROR: The following columns were not found in the contributing tables: Null "

I then changed Null to '.', I got this error : "ERROR: Result of WHEN clause 2 is not the same data type as the preceding results."

If I was to connect to table LN_REGULAT directly from the data source, this case statement works.

but if I create the table as (select * form connection......), and then run this case statement, it doesn't work.

Could you please help?

Thanks,

Re: Proc SQL CASE statement gives errors

Reeza — Wed, 15 May 2019 15:41:20 GMT

SAS doesn't have a concept of NULL, instead replace it with quotation marks with nothing in between them, ie "".
You likely don't want quotes around your new name either.

Case when LN_REGULAT.ACTION_TAKEN in ( 3, 7) then LN_REGULAT.ACTION_DATE else "" end as DENIED_DATE

Re: Proc SQL CASE statement gives errors

MelissaN — Wed, 15 May 2019 15:48:49 GMT

Thanks for you response.

I got this error instead

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

LN_REGULAT.ACTION_DATE is a datetime value

Re: Proc SQL CASE statement gives errors

Reeza — Wed, 15 May 2019 15:50:16 GMT

Ah, then the quotes should be a period instead.

Case when LN_REGULAT.ACTION_TAKEN in ( 3, 7) then LN_REGULAT.ACTION_DATE else . end as DENIED_DATE

Re: Proc SQL CASE statement gives errors

Tom — Wed, 15 May 2019 15:55:35 GMT

For your examples you can just eliminate the ELSE clause and the resulting value will be missing without you having to know whether you want numeric or character missing. You might get a note about the missing ELSE but it should work.

,case when LN_REGULAT.ACTION_TAKEN in (3,7) then LN_REGULAT.ACTION_DATE 
 end 'DENIED_DATE' 
,case when LN_REGULAT.ACTION_TAKEN in (2,4,5,8) then LN_REGULAT.ACTION_DATE 
 end 'WITHDRAWN_DATE'

Re: Proc SQL CASE statement gives errors

MelissaN — Wed, 15 May 2019 15:59:30 GMT

This also works as well. Thanks for your help.