Proc SQL CASE statement gives errors

[MelissaN]

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,

[Reeza]

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

[MelissaN]

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

[Reeza · Accepted solution]

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

[Tom]

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'

[MelissaN]

This also works as well. Thanks for your help.