Help in Case Statement

[Kalai2008]

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

[art297]

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

[Kalai2008]

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,

[Astounding]

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.

[Kalai2008]

Thank you! I agree to your point.

[ChrisBrooks]

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....