Error: Result of When clause 2 is not the same data type as the preceding results

[cmshearon8845]

Hi,

I received this error when I tried to do a select distinct in proc SQL: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results. I'm programming in SAS Enterprise Guide 8.3.

Here is my code:

proc sql;
create table OneTerm as
select drv.TERMS_ID, drv.day, (drv.day+drv.start_dt) as date format
YYMMDD10., drv.SECTID,
case when a.CreditHours is missing then 0 else a.CreditHours end as CreditHours,
case when a.seatcount is missing then 0 else a.seatcount end as seatcount from(select distinct a.TERMS_ID,a.day,
(a.date-a.day)as start_dt format YYMMDD10., b.SECTID
from work.ADDREGDAY as a left join
(select distinct TERMS_ID, SECTID from work.addregday where TERMS_ID="&TERMS_ID" and SECTID is not null)b
on a.TERMS_ID=b.TERMS_ID where a.TERMS_ID="&Terms_ID") as drv
left join
(select * from work.addregday where TERMS_ID="&TERMS_ID") as a
on drv.day=a.day and drv.SECTID=a.SECTID;
quit;

Here is the logs:

proc sql;
277 create table OneTerm as
278 select drv.TERMS_ID, drv.day, (drv.day+drv.start_dt)
41 The SAS System
12:22 Friday, October 22, 2021

278 ! as date format
279 YYMMDD10., drv.SECTID,
280 case when a.CreditHours is missing then 0 else
280 ! a.CreditHours end as CreditHours,
281 case when a.seatcount is missing then 0 else
281 ! a.seatcount end as seatcount from(select distinct
281 ! a.TERMS_ID,a.day,
282 (a.date-a.day)as start_dt format YYMMDD10., b.SECTID
283 from work.ADDREGDAY as a left join
284 (select distinct TERMS_ID, SECTID from work.addregday
284 ! where TERMS_ID="&TERMS_ID" and SECTID ne .)b
285 on a.TERMS_ID=b.TERMS_ID where
42 The SAS System
12:22 Friday, October 22, 2021

285 ! a.TERMS_ID="&Terms_ID") as drv
286 left join
287 (select * from work.addregday where
287 ! TERMS_ID="&TERMS_ID") as a
288 on drv.day=a.day and drv.SECTID=a.SECTID;
ERROR: Result of WHEN clause 2 is not the same data type as the
preceding results.
NOTE: PROC SQL set option NOEXEC and will continue to check the
syntax of statements.
289 quit;
NOTE: The SAS System stopped processing this step because of
errors.

Could someone help me with what's going on? 

SECTID is a numeric

Day is a numeric

TERMS_ID is a character

I appreciate any help you can provide.

[PaigeMiller · Accepted solution]

case when a.seatcount is missing then 0 else a.seatcount end as seatcount

a.seatcount is likely a character variable, and you can't sometimes assign a.seatcount (a character value) and sometimes assign 0 (a numeric value) to the same variable

Make a.seatcount into a numeric variable.

[ballardw]

You might want to think about your definition of the alias A.

(a.date-a.day)as start_dt format YYMMDD10., b.SECTID
from work.ADDREGDAY as a left join
(select distinct TERMS_ID, SECTID from work.addregday where TERMS_ID="&TERMS_ID" and SECTID is not null)b
on a.TERMS_ID=b.TERMS_ID where a.TERMS_ID="&Terms_ID") as drv
left join
(select * from work.addregday where TERMS_ID="&TERMS_ID") as a
on drv.day=a.day and drv.SECTID=a.SECTID;
quit;

With more than one A are you sure that the CASE statement is using the right one? If both A have a variable in common but different types that could be an issue.

Back out the multiple joins to just the variables in the Case statements and test. I am not  sure why you bothered to point out those variables, the Case statement When clauses are using Credithours and Seatcount. Those would be variables to look at.

Please consider formatting your code as it is very hard to follow. Paste code into a code box opened on the forum with the "running man" or </> icons that appear above the message windows. Log text should go into a text box opened with the </> icon.