topic Re: Help in Case Statement in SAS Programming

Help in Case Statement

Kalai2008 — Mon, 17 Jul 2017 22:41:23 GMT

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

Re: Help in Case Statement

art297 — Mon, 17 Jul 2017 22:58:35 GMT

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

Re: Help in Case Statement

Astounding — Tue, 18 Jul 2017 02:21:13 GMT

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.

Re: Help in Case Statement

ChrisBrooks — Mon, 17 Jul 2017 23:39:34 GMT

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

Re: Help in Case Statement

Kalai2008 — Tue, 18 Jul 2017 00:59:14 GMT

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,

Re: Help in Case Statement

Kalai2008 — Tue, 18 Jul 2017 01:15:49 GMT

Thank you! I agree to your point.