How to apply multiple set property using proc sql?

[nikunjgattani]

Input -

I have two datasets -

Data Tab1;

input id x $;

cards;

1 a

2 b

3 c

4 d

5 e

;

run;

Data Tab2;

input y $;

hh

ii

jj

uu

kk

;

Outuput -

Tab3 -

id x y

1 a hh

2 b ii

3 c jj

4 d uu

5 e kk

It can be done by using multiple set statement. But, how can we do it by using proc sql?

[RW9 · Accepted solution]

Firstly I don't recommend merging data based on an adhoc position in a dataset.  If the sequence changes, say extra rows added, or sorted differently you will end up with different outcome, hence this kind of merge is dangerous.

If however you are set on this course of action:

proc sql;

     create table WANT as

     select     A.ID,

                    A.X,

                    B.Y

     from       (select *,MONOTONIC() as _N from DS1) A

     left join  (select *,MONOTONIC() as _N from DS2) B

     on          A._N=B._N;

quit;

Note the monotonic function is currently unsupported and may return un-expected results.  The underlying fact about merging is that there should be a logical set of matching identifier variables in each table to match against.

[Ksharp]

You need to create a index variable ID in Tab1 as in Tab2;

Data Tab1;

input id x $;

cards;

1 a

2 b

3 c

4 d

5 e

;

run;

Data Tab2;

input y $;

cards;

hh

ii

jj

uu

kk

;

data tab2;

set tab2;

id+1;

run;

proc sql;

create table want as

  select *

   from tab1 natural join tab2 ;

quit;

Xia Keshan

[Kurt_Bremser]

Use the right tool, which in this case is the data step with a merge; no need for SQL.

Of course you can try to put the screw into the wood by detonating a pound of dynamite on top of it, but a screwdriver is the better option.

[RW9]

To re-write an old favourite - select needle from haystack

How about - insert screw into wood

What could be simpler?