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?
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.
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.
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
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.
To re-write an old favourite - select needle from haystack
How about - insert screw into wood
What could be simpler?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.