topic NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results. in SAS Programming

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Dipu — Sun, 09 Feb 2014 02:02:19 GMT

Hello Please guide me with these-

I am using this code-

Proc sql;

create table Table2 as

Select Week1,week2 ,

count(distinct case when trim(order)= 'New' then Cust end) as Var1 ,

count(distinct case when trim(order)= 'New' then order1 else 0 end) as Neworder,

sum(case when trim(order)= 'New' then quantity else 0 end) as Newquan ,

sum(case when trim(order)= 'New' then amount else 0 end) as Newamt,

From Table1

Group by week1 , week2;

quit;

I am getting the following error.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression.

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

Also I am getting this error in other part of the code.

ERROR: The SUM summary function requires a numeric argument.

Thank you

Dipu

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

PGStats — Sun, 09 Feb 2014 03:07:44 GMT

If Cust is numeric, replace

case when trim(order)= 'New' then Cust end

with

case when trim(order)= 'New' then Cust else . end

if Cust is character, replace it with

case when trim(order)= 'New' then Cust else "" end

Also check that order1, quantity and amount are numeric. In the case expressions the type of what you put after THEN must be the same as the type of what appears after ELSE.

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Dipu — Sun, 09 Feb 2014 03:56:25 GMT

Thank you very much PG.

Proc sql;

create table Table2 as

Select Week1,week2 ,

count(distinct case when trim(order)= 'New' then Cust end) as Var1 , *** Cust is numeric***;

count(distinct case when trim(order)= 'New' then order1 else 0 end) as Neworder, ***Problem lies here order1 is char instead of count(distinct case when trim(order)= 'New' then order1 else 0 end) as Neworder

I used

count(distinct case when trim(order)= 'New' then order1 else "" end) as Neworder,

***;

sum(case when trim(order)= 'New' then quantity else 0 end) as Newquan ,

sum(case when trim(order)= 'New' then amount else 0 end) as Newamt,

From Table1

Group by week1 , week2;

quit;

But now I am getting this error-

for the statement-

,sum(case when trim(order)= 'New' then order1 else 0 end) as Exorders

where order is char but it contains values like PL123, PL435, PL433 and order1 is char.

Please guide

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

PGStats — Sun, 09 Feb 2014 04:19:34 GMT

Try this. If it doesn't work, please post log.

Proc sql;

create table Table2 as

Select Week1, week2,

count(distinct case when trim(order)= 'New' then Cust else . end) as Var1,

count(distinct case when trim(order)= 'New' then order1 else " " end) as Neworder,

sum(case when trim(order)= 'New' then quantity else 0 end) as Newquan,

sum(case when trim(order)= 'New' then amount else 0 end) as Newamt

From Table1

Group by week1, week2;

quit;

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Dipu — Sun, 09 Feb 2014 05:09:57 GMT

Hello PG,

Actually What you mentioned in the previous reply helped me to solve my query. This is another problem in continuing the query
Please have a look on this

,count(distinct case when trim(order)= 'Existing' then cust end) as Exicust **It is working**;

,count(distinct case when trim(order)= 'Existing' then order1 else "" end) as Exiorder

,sum(case when trim(order)= 'Existing' then order1 else 0 end) as Exi_orders *** Here is the problem since order and order1 both are char and order1 has values like Pl123, pl345 etc.***(If I comment this line then it works)

,sum(case when trim(order)= 'Existing' then quantity else 0 end) as Exi_units

,sum(case when trim(order)= 'Existing' then amount else 0 end) as Exi_dollars

here is the log

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

ERROR: The SUM summary function requires a numeric argument.

Thank you

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

PGStats — Sun, 09 Feb 2014 16:33:43 GMT

It is not another problem. It is the same problem. The solutions explained above will work here too. - PG

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Dipu — Sun, 09 Feb 2014 17:45:31 GMT

Hello PG

Thank you for your suggestions.

Actually after using the same code as you said, it did reduced couple of errors but am still getting errors for this-

,count(distinct case when trim(order)= 'Existing' then cust end) as Exicust **It is working**;

,count(distinct case when trim(order)= 'Existing' then order1 else "" end) as Exiorder

,sum(case when trim(order)= 'Existing' then order1 else " " end) as Exi_orders *** Here is the problem since order and order1 both are char and order1 has values like Pl123, pl345 etc.***(If I comment this line then it works)

,sum(case when trim(order)= 'Existing' then quantity else 0 end) as Exi_units

,sum(case when trim(order)= 'Existing' then amount else 0 end) as Exi_dollars

I am getting this error-

for the bolded statement and if I comment that statement then the program runs successfully.

ERROR: The SUM summary function requires a numeric argument.

Thank you

Re: NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Dipu — Sun, 09 Feb 2014 20:14:19 GMT

Thank you PG. I think that was the most correct solution you told me.

Thanks

Dipu