Help using Base SAS procedures

error in sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

error in sql

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;


Accepted Solutions
Solution
‎10-28-2014 02:47 AM
Super User
Posts: 7,824

Re: error in sql

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

  No

  CustomeriD

  Product

  Category

  Age

  Income

  DSA

  City

  SECODE

  SEAGE

  Qualification

  Pricing

;

cards;

1,MUM001,Savings Account,NMC,21-30,388.662,ZARETH,MUM,Z9,59,4,Discount

2,MUM002,LIC,NMC,51-60,98.530,CRACKF,MUM,C2,37,2,Discount

;

run;

proc sql;

create view want as

select product, category,income, age,

case

when age eq 'gt 60' then 'Top'

when age eq '51-60' then 'Medium'

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

else 'Low'

end as Aged

from have;

quit;

proc print data=want noobs;

run;

and renders the following result:

Product                 Category   IncomeAge  Aged

Savings Account           NMC     388.66221-30Low 
LIC                       NMC      98.53051-60Medium
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: error in sql

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

Frequent Contributor
Posts: 92

Re: error in sql

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.

Trusted Advisor
Posts: 1,228

Re: error in sql

What is the type of age variable?

Frequent Contributor
Posts: 92

Re: error in sql

it is a character variable

Trusted Advisor
Posts: 1,228

Re: error in sql

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

Super User
Posts: 7,824

Re: error in sql

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 92

Re: error in sql

Posted in reply to KurtBremser

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

Solution
‎10-28-2014 02:47 AM
Super User
Posts: 7,824

Re: error in sql

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

  No

  CustomeriD

  Product

  Category

  Age

  Income

  DSA

  City

  SECODE

  SEAGE

  Qualification

  Pricing

;

cards;

1,MUM001,Savings Account,NMC,21-30,388.662,ZARETH,MUM,Z9,59,4,Discount

2,MUM002,LIC,NMC,51-60,98.530,CRACKF,MUM,C2,37,2,Discount

;

run;

proc sql;

create view want as

select product, category,income, age,

case

when age eq 'gt 60' then 'Top'

when age eq '51-60' then 'Medium'

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

else 'Low'

end as Aged

from have;

quit;

proc print data=want noobs;

run;

and renders the following result:

Product                 Category   IncomeAge  Aged

Savings Account           NMC     388.66221-30Low 
LIC                       NMC      98.53051-60Medium
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 92

Re: error in sql

Posted in reply to KurtBremser

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

Super User
Posts: 5,513

Re: error in sql

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1408 views
  • 0 likes
  • 4 in conversation