I have these 02 tables:
Both Entrances
N | 21 | 5 | 41 | 1 |
N | 65 | 2 | 67 | 1 |
N | 66 | 2 | 7 | 1 |
N | 87 | 4 | 33 | 3 |
S | 43 | 3 | 27 | . |
S | 44 | 3 | 24 | . |
S | 45 | 3 | 2 | . |
Both Entrances
S | 43 | 3 | 27 | . | 35 |
S | 44 | 3 | 24 | . | 35 |
S | 45 | 3 | 2 | . | 0 |
N | 21 | 5 | 41 | 1 | 35 |
N | 87 | 4 | 33 | 3 | 35 |
N | 65 | 2 | 67 | 1 | 27 |
N | 66 | 2 | 7 | 1 | 35 |
Maxim 2: read the log.
Reading the log will alert you to your syntax error. if/then is not SQL syntax, conditional processing in SQL is done with a case expression:
proc sql;
create table both as
select
*,
. as lot,
case
when age = . then .
when age < 3 then 0
when age >= 3 and age < 65 then 35
else 27
end as amountpaid
from southentrance
union all
select
*,
case
when age = . then .
when age < 3 then 0
when age >= 3 and age < 65 then 35
else 27
end as amountpaid
from northentrance;
quit;
Note how complicated this rather simple operation gets in SQL. The proper tool for this is a data step. Also see Maxim 14.
First, and far more important than what your code does, is good presentation of code, e.g. not shouting code at us, finishing blocks, using the code window (its the {i} above post area) etc. Code is communication and should follow the same rules as communication.
Next up, your terminology is a bit off. In the code presented you are appending data from one dataset to another, not joining the data. I.e. you are putting the second datasets data under the first, not merging columns on based on a series of IDs.
Next, SQL is not a linear process like datastep. If you add _tree _method to the proc sql line you can actually see what modules it is calling behind the scenes, and can include sorts and such like. As you are using - which isn't good practice - select * or select everything, within the various components it will select the variables as and when its needs them for that block.
Resolution, unless you need to use SQL for something specific, then it is best to use Base SAS - which is the programming language here. SQL does have its uses, but in this particular task it is not the best utility, or even the second best:
So the way to append these here would be datastep, or using proc append then adding the manipulation to a future datastep.
I would finally point out that the code you presented for sql is invalid, if statement cannot appear in open code like that:
select * from northentrance; /* Here */ IF Age is null THEN AmountPaid is null ELSE IF Age < 3 THEN AmountPaid = 0 ELSE IF Age < 65 THEN AmountPaid = 35 ELSE AmountPaid = 27; /* To here */ quit;
Maxim 2: read the log.
Reading the log will alert you to your syntax error. if/then is not SQL syntax, conditional processing in SQL is done with a case expression:
proc sql;
create table both as
select
*,
. as lot,
case
when age = . then .
when age < 3 then 0
when age >= 3 and age < 65 then 35
else 27
end as amountpaid
from southentrance
union all
select
*,
case
when age = . then .
when age < 3 then 0
when age >= 3 and age < 65 then 35
else 27
end as amountpaid
from northentrance;
quit;
Note how complicated this rather simple operation gets in SQL. The proper tool for this is a data step. Also see Maxim 14.
Hi Kurt Bremser
Thanks for your help it worked.
I forgot to use case with sql in he proc step.
Just one question.
why we have to
select *, . as lot,
and then in the other statement we only use
select *,
why select * cant bring everything in.
Please advise.
thanks
Tauqeer
Apply Maxim 4 and test it, then apply Maxim 2 and look at the log.
If you omit the
. as lot
you get a WARNING, which is a violation of Maxim 25; much more important: you get wrong values, because "lot" ist stacked with amountpaid.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.