join two tables using proc sql

[bondtk]

I have these 02 tables:

south
S 43 3 27
S 44 3 24
S 45 3  2

north
N 21 5 41 1
N 87 4 33 3
N 65 2 67 1
N 66 2  7 1

created these 02 sets:

DATA southentrance;
   INFILE '/folders/myfolders/sas littlebook/South.dat';
   INPUT Entrance $ PassNumber PartySize Age;
PROC PRINT DATA = southentrance;
   TITLE 'South Entrance Data';
RUN;
DATA northentrance;
   INFILE '/folders/myfolders/sas littlebook/North.dat';
   INPUT Entrance $ PassNumber PartySize Age Lot;
PROC PRINT DATA = northentrance;
   TITLE 'North Entrance Data';
RUN;

now I want to join them with proc sql and adding new variable:  its combining them but new variable is not added

and sequence is bit odd as well after combine,  please advise why there is a difference in sequence as well and the new variable

is not added as well.

proc sql;
create table both as
select * from southentrance
union
select * from northentrance;
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;
quit;
PROC PRINT DATA = both;
   TITLE 'Both Entrances';
RUN;

output:

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	.

if I use sas instead of sql then it works fine.

DATA both;
   SET southentrance northentrance;
   IF Age = . THEN AmountPaid = .;
      ELSE IF Age < 3  THEN AmountPaid = 0;
      ELSE IF Age < 65 THEN AmountPaid = 35;
      ELSE AmountPaid = 27;
RUN;
PROC PRINT DATA = both;
   TITLE 'Both Entrances';
RUN;

output: 

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

[RW9]

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;

[Kurt_Bremser · Accepted solution]

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.

[bondtk]

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

[Kurt_Bremser]

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.