DATA Step, Macro, Functions and more

SQL merge 2 data fully

Accepted Solution Solved
Reply
Super Contributor
Posts: 402
Accepted Solution

SQL merge 2 data fully

Hi,

 

I remember that there is a quick way to do SQL to accomplish the merge 2 datasets as I did below.

I just cant figure out what is the syntax, basically, I dont need to use the variable x=1;

Thank you,

 

HC

 

 

data a;
input   number;
datalines;
1
3
102
;run;

data b;
input  name1 :$5. ;
datalines;
new1
new2
;run;

data a; set a; x=1; run;
data b; set b; x=1; run;

proc sql;
create table want (drop=x)as select * from a join b
on a.x=b.x;quit;

Accepted Solutions
Solution
2 weeks ago
Valued Guide
Posts: 947

Re: SQL merge 2 data fully

Change the "ON" expression to any tautology:

 

data a;
input   number;
datalines;
1
3
102
;run;

data b;
input  name1 :$5. ;
datalines;
new1
new2
;run;

proc sql;
  create table want as select * from a join b on 1=1;
quit;

 

In fact  you could say   "ON 0=0"    or   "ON 1>0".

View solution in original post


All Replies
Solution
2 weeks ago
Valued Guide
Posts: 947

Re: SQL merge 2 data fully

Change the "ON" expression to any tautology:

 

data a;
input   number;
datalines;
1
3
102
;run;

data b;
input  name1 :$5. ;
datalines;
new1
new2
;run;

proc sql;
  create table want as select * from a join b on 1=1;
quit;

 

In fact  you could say   "ON 0=0"    or   "ON 1>0".

Super Contributor
Posts: 402

Re: SQL merge 2 data fully

Thank you,

nice trick.

HC

Super User
Posts: 5,362

Re: SQL merge 2 data fully

Are you just looking for this?  Just create data sets A and B, and don't create X:

 

proc sql;

create table want as select * from a, b;

quit;

Super Contributor
Posts: 402

Re: SQL merge 2 data fully

YES, it is what I am looking for.

 

from a, b;

 

Thanks,

HC

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 143 views
  • 1 like
  • 3 in conversation