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;
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 | Income | Age | Aged |
Savings Account | NMC | 388.662 | 21-30 | Low |
LIC | NMC | 98.530 | 51-60 | Medium |
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
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.
What is the type of age variable?
it is a character variable
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
What is your ERROR message and could you _please_ supply test data?
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
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 | Income | Age | Aged |
Savings Account | NMC | 388.662 | 21-30 | Low |
LIC | NMC | 98.530 | 51-60 | Medium |
thank you for such an extensive explanation. its hit the nail.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.