topic Re: using case when to create new variable in SAS Programming

using case when to create new variable

Jedrzej — Tue, 30 Jan 2018 09:30:21 GMT

Hi,

i have two data sets

set one

ID Type Amount

1 AB 1000

2 AC 2000

3 AD 3000

4 AF 4000

5 AE 5000

set two

In the first data set, observations in variable "type" are a symbol for example AB stands for "loan".

Now i want to join these sets on ID and create a new variable based on a type variable from set one.

example.

data want

ID Loan

1 1000

I've tried to do it with case when

proc sql;

create table transactions as select a.*,

(case

when b.type='AB' then b.Amount end) as loan

from set_one a

left join

set_two b

on a.ID=b.ID;

quit;

and it works, but I want it to be more efficient and added more "when"

proc sql;

create table transactions as select a.*,

(case

when b.type='AB' then b.Amount end) as loan

when b.type='AC' then b.Amount end) as debit

from set_one a

left join

set_two b

on a.ID=b.ID;

quit;

it gave me a syntax error, probably cause the end statement in loan step.

Is there another way to solve this problem, how can I join these tables ?

Re: using case when to create new variable

Satish_Parida — Tue, 30 Jan 2018 09:35:17 GMT

This is the correct way of doing it. Hope it helps.


proc sql;

create table transactions as select a.*,

(case when b.type='AB' then b.Amount end) as loan,

(case when b.type='AC' then b.Amount end) as debit

from set_one a

left join

 set_two  b

on a.ID=b.ID;

quit;

Re: using case when to create new variable

Kurt_Bremser — Tue, 30 Jan 2018 09:37:26 GMT

Each case must have its own end, and each end must have a case. You can't have one case with two ends.

The brackets around the case - end blocks are not necessary

For clarity, write it like that:

case
  when (condition) then ....
  when (condition) then ...
  else ...
end as newvar

Re: using case when to create new variable

Jedrzej — Tue, 30 Jan 2018 09:38:08 GMT

it doesn't, tried this also

_ ____ _

22 201 76

ERROR 22-322: Syntax error, expected: ',', FROM.

ERROR 201-322: The option is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

Re: using case when to create new variable

RW9 — Tue, 30 Jan 2018 09:38:14 GMT

Use the code window (its the {i} above post area) when posting code:

proc sql; 
  create table transactions as 
  select a.*,
         case when b.type='AB' then b.Amount else . end as loan,
         case when b.type='AC' then b.Amount else . end as debit 
  from   set_one a
  left join  set_two  b
  on     a.id=b.id;
quit;

So to create a variable it is one line on its own, with its own case/when/end block. Do note you don't need all the extra brackets and what not.

Re: using case when to create new variable

Jedrzej — Tue, 30 Jan 2018 09:40:12 GMT

ok, i've changed that every case will have it own end like Satish wrote and i have an error.

proc sql;

create table transactions as select a.*,

(case when b.type='AB' then b.Amount end) as loan,

(case when b.type='AC' then b.Amount end) as debit

from set_one a

left join

set_two b

on a.ID=b.ID;

quit;

Re: using case when to create new variable

Satish_Parida — Tue, 30 Jan 2018 09:40:37 GMT

It missed a comma in line4, please try now

Re: using case when to create new variable

Kurt_Bremser — Tue, 30 Jan 2018 09:41:18 GMT

@Jedrzej wrote:

it doesn't, tried this also

_ ____ _

22 201 76

ERROR 22-322: Syntax error, expected: ',', FROM.

ERROR 201-322: The option is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

When posting an ERROR, ALWAYS:

post the whole log of the failed step
use the {i} button, so that the formatting of the log is preserved; this is crucial in identifying the offending codepiece

Re: using case when to create new variable

Jedrzej — Tue, 30 Jan 2018 09:43:05 GMT

works, thx