DATA Step, Macro, Functions and more

using case when to create new variable

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

using case when to create new variable

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

 

ID

1

2

3

4

5

 

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 ?

 

 

 

 


Accepted Solutions
Solution
‎01-30-2018 04:42 AM
Super User
Super User
Posts: 9,397

Re: using case when to create new variable

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 109

Re: using case when to create new variable

[ Edited ]

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;
Contributor
Posts: 26

Re: using case when to create new variable

Posted in reply to Satish_Parida

 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.

Frequent Contributor
Posts: 109

Re: using case when to create new variable

It missed a comma in line4, please try now
Super User
Posts: 9,868

Re: using case when to create new variable


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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,868

Re: using case when to create new variable

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 26

Re: using case when to create new variable

Posted in reply to KurtBremser
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;
Solution
‎01-30-2018 04:42 AM
Super User
Super User
Posts: 9,397

Re: using case when to create new variable

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.

Contributor
Posts: 26

Re: using case when to create new variable

works, thx
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 152 views
  • 0 likes
  • 4 in conversation