topic Case When Expression and multiple data types in SAS Procedures

Case When Expression and multiple data types

Airintheballoon — Tue, 04 Oct 2016 21:49:28 GMT

Hi All -

Cannot get past "ERROR: Result of WHEN clause 2 is not the same data type as the preceding results." Stumped with this sequence of events....

Prior PROC SQL step:

..............

input(A.INITIAL_DELV_D,yymmdd8.) as INIT_DELV_DT length=8 label="INIT_DELV_DT" format=DATE9. informat=DATE9.,
input(A.CURR_DELIVERY_D,yymmdd8.) as CURR_DELV_DT length=8 label="CURR_DELV_DT" format=DATE9. informat=DATE9.

.............. All looks good after this step......

Current PROC SQL step:

Proc sql;
Create Table REVENUE_EW As
Select DISTINCT A.FISCAL_YEAR, A.FISCAL_MONTH, A.SERNO, A.INIT_DELV_DT, A.CURR_DELV_DT, A.AMOUNT,
CASE When B.ALT_EXP_DT IS NOT NULL Then B.ALT_EXP_DT Else B.EXPIRATION_DT End AS SECURE_EXP_DT "SECURE_EXP_DT" format=DATE9.,
CASE WHEN A.TYPE_ID = 1 THEN A.BASIC_ST_DT WHEN A.TYPE_ID = 2 THEN A.CURR_DELV_DT Else " " END AS SECURE_ST_DT "SECURE_ST_DT" format=Date9.,
CASE WHEN A.TYPE_ID = 1 THEN "NEW, TRANSFER, UPGRADE" WHEN A.TYPE_ID = 2 THEN "CERTIFIED USED" Else " " END AS TYPE "TYPE",
From REVENUE1 A;
Quit;

After informating CURR_DELV_DT, it has the same length, format and informat as BASIC_ST_DT. Not sure where the obvious issue is. Thanks in advance.

Re: Case When Expression and multiple data types

Reeza — Tue, 04 Oct 2016 22:37:35 GMT

If you format your code, and use a code block, the little i or paper icon 6/7 icon at top of the editor, the code wlll be readable. As is, it's hard to decipher.

In fact, you can usually find the difference and answer pretty quickly once it's readable.

Re: Case When Expression and multiple data types

Reeza — Tue, 04 Oct 2016 22:41:33 GMT

Its the ELSE " " END that's the issue. Your variable is numeric and missing in SAS for numeric is .

So it should be

Else .

END

Re: Case When Expression and multiple data types

Airintheballoon — Tue, 04 Oct 2016 23:48:44 GMT

I knew it was going to be obvious.... Thanks everyone!