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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.