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 ?
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.
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;
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.
@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:
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
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.