I am using Proc Sql.
I am getting an error while trying to use this case_statement..
proc sql;
create table test
as select a.*,
case
when code like 'a' and amt <= 200 then 1 end as Code A (Less than 200)
when code like 'a' and amt > 200 then 1 end as Code A (More than 200)
when code like 'b' and amt <= 200 then 1 end as Code B (Less than 200)
when code like 'b' and amt > 200 then 1 end as Code B (More than 200)
from t.test1 a ;
run;
Given Data:
Acct Amt Code
11 195 a
22 205 b
22 50 a
33 300 a
Output like:
Acct Code A (Less than 200) Code A (More than 200) Code B (less than 200) Code B (More than 200)
11 1 0 0 0
22 1 0 0 1
33 0 1 0 0
A few minor (?) changes to your code:
proc sql; create table test as select a.*, case when code like 'a' and amt <= 200 then 1 end as Code_A_Less_than_200, case when code like 'a' and amt > 200 then 1 end as Code_A_More_than_200, case when code like 'b' and amt <= 200 then 1 end as Code_B_Less_than_200, case when code like 'b' and amt > 200 then 1 end as Code_B_More_than_200 from t.test1 a ; run;
Art, CEO, AnalystFinder.com
Thank you for the reply.
Just few more questions,
I need to add a new calculated column called Sum , not sure if I can add them in the case statement.
Output like:
Acct Code A (Less than 200) Code A (More than 200) Code B (less than 200) Code B (More than 200) tot_sum
11 1 0 0 0 195
22 1 0 0 1 255
33 0 1 0 0 300
Can we also replace the code with between ...I am getting error if I use between.
case when code like 'a' and amt between 0-200 then 1 end as Code_A_Less_than_200,
case when code like 'a' and amt between 201-300 then 1 end as Code_A_More_than_200,
or something like this?
case when code like 'a' and amt < 200 then 1 end as code_a_less_than_200,
case when code like 'a' and 200 >amt<201 then 1 end as code_a_more tha_200,
In addition to the changes that Art mentioned, also notice ...
Your code never assigns a value of 0 to any of the variables.
It's acceptable, but very unusual to use a LIKE comparison when you are looking for an exact match.
Here is a suggestion:
proc sql; create table test as select a.*, (case when code='a' and amt <= 200 then 1 else 0 end) as Code_A_Less_than_200, (case when code='a' and amt > 200 then 1 else 0 end) as Code_A_More_than_200, (case when code='b' and amt <= 200 then 1 else 0 end) as Code_B_Less_than_200, (case when code='b' and amt > 200 then 1 else 0 end) as Code_B_More_than_200 from t.test1 a ; run;
If you want TOT_SUM, you have to tell us what it represents.
Just another little point - you don't have an else condition in your case statement. Even if you're confident all rows match one of the conditions it's considered good practice to include an "else" just in case....
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.