topic Re: error in sql in SAS Procedures

error in sql

Shivi82 — Mon, 27 Oct 2014 14:31:50 GMT

proc sql;

create view final as

select product, category,income, age,

case

when age eq 'gt 60' then 'Top'

when age '51-60' then 'Medium'

when age '41-50' then 'Below Medium'

else 'Low'

end as Aged

from sss.sales;

Re: error in sql

stat_sas — Mon, 27 Oct 2014 14:36:47 GMT

Seems like your age variable is numeric. Modify your case statement.

when age >= 60 then 'Top'

when age between 51 and 60 then 'Medium'

when age between 41 and 50 then 'Below Medium'

else 'Low'

end as Aged

Re: error in sql

Shivi82 — Mon, 27 Oct 2014 15:04:34 GMT

thanks. this seems to be a correct approach however when i applied the code as above I get the below error message:

ERROR: Expression using greater than (>) has components that are of different data types.

ERROR: Expression using IN has components that are of different data types.

Re: error in sql

stat_sas — Mon, 27 Oct 2014 15:23:51 GMT

What is the type of age variable?

Re: error in sql

Shivi82 — Mon, 27 Oct 2014 15:39:06 GMT

it is a character variable

Re: error in sql

Kurt_Bremser — Mon, 27 Oct 2014 15:49:33 GMT

What is your ERROR message and could you _please_ supply test data?

Re: error in sql

Astounding — Mon, 27 Oct 2014 15:52:17 GMT

It can't possibly be correct to use EQ for one comparison and then omit EQ for the other comparisons. Whichever is the right syntax, you have to be consistent.

Good luck.

Re: error in sql

stat_sas — Mon, 27 Oct 2014 15:59:41 GMT

So if your variable is char and contains numeric values only then you can modify your case statement like this.

when input(age,8.) >= 60 then 'Top'

when input(age,8.) between 51 and 60 then 'Medium'

when input(age,8.) between 41 and 50 then 'Below Medium'

else 'Low'

end as Aged length=15

Re: error in sql

Shivi82 — Mon, 27 Oct 2014 16:08:30 GMT

Hi Kurt, the error message is :

ERROR: Expression using greater than (>) has components that are of different data types.ERROR: Expression using IN has components that are of different data types

NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL

WHERE clause optimization

Re: error in sql

Kurt_Bremser — Tue, 28 Oct 2014 06:47:12 GMT

This works:

data have;

infile cards dlm=',';

format

No 3.

CustomerID $6.

Product $25.

Category $3.

Age $6.

Income commax10.

DSA $6.

City $3.

SECODE $2.

SEAGE 3.

Qualification 2.

Pricing $10.

;

informat income commax10.; * because of dot;

input

CustomeriD

Product

Re: error in sql

Shivi82 — Tue, 28 Oct 2014 17:41:56 GMT

thank you for such an extensive explanation. its hit the nail.

Savings Account	NMC	388.662	21-30	Low
LIC	NMC	98.530	51-60	Medium