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]

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

[PGStats · Accepted solution]

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.

PG

[Dipu]

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

[PGStats]

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;

PG

[Dipu]

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

[PGStats]

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

[Dipu]

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

[Dipu]

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

Thanks

Dipu