BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shivi82
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

10 REPLIES 10
stat_sas
Ammonite | Level 13

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

Shivi82
Quartz | Level 8

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.

stat_sas
Ammonite | Level 13

What is the type of age variable?

Shivi82
Quartz | Level 8

it is a character variable

stat_sas
Ammonite | Level 13

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

Shivi82
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Shivi82
Quartz | Level 8

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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