Matching all records in data1 to one row dataset data2 on variable list x1-x&k

[RobertWF1]

Say I have simulated data:

data d1;
do i=1 to 1000;
  x1=rand('binomial',.1,1);
  x2=rand('binomial',.3,1);
  x3=rand('binomial',.6,1);
  x4=rand('binomial',.8,1);
  y=x1+x2+x3+x4;
output;
end;
run;

and a second one row dataset:

data d2;
input x1 x2 x3 x4;
cards;
0 0 0 0
;
run;

and I want join on the list of all x variables x1-x4 in d2 without typing a long ON statement:

%let k=4;
proc sql;
create table join1 as
select a.*, b.*
from d1 a, d1 b
on x1:x&k;
quit;

Is there a quick way to do this or do I need to perhaps use a macro to construct a string "on a.x1=b.x1, a.x2=b.x2, . . ."?

Thanks!

[sbxkoenk · Accepted solution]

Hello,

Is this what you want? :

data d1;
do i=1 to 1000;
  x1=rand('binomial',.1,1);
  x2=rand('binomial',.3,1);
  x3=rand('binomial',.6,1);  
  x4=rand('binomial',.8,1);
  y=x1+x2+x3+x4;
output;
end;
run;

data d2;
input x1 x2 x3 x4;
cards;
0 0 0 0
;
run;

PROC SQL noprint;
 select 'a.' || strip(name) || '=b.' || strip(name) into :mymv separated by ' AND '
 from dictionary.columns
 where libname='WORK' and memname='D2';
QUIT;
%PUT &=mymv;

*%let k=4;
proc sql;
create table join1 as
select a.* /* , b.* */
from d1 as a, d2 as b
where &mymv.;
quit;
/* end of program */

Koen

[RobertWF1]

Perfect!

But how exactly does the following code work?

PROC SQL noprint;
select 'a.' || strip(name) || '=b.' || strip(name) into :mymv separated by ' AND '
from dictionary.columns
where libname='WORK' and memname='D2';
QUIT;

I'm not familiar with "dictionary.columns", "names", and "memnames" - are they new features in SAS that let you work with metadata?

[sbxkoenk]

Hello @RobertWF1 ,

The dictionary tables have always been part of SAS (afaik).

The SASHELP library has views on these dictionary tables.

Look for example at SASHELP.VCOLUMN (dictionary.columns), SASHELP.VTABLE (dictionary.tables), SASHELP.VLIBNAM etc ...
See here :
SAS® 9.4 and SAS® Viya® 3.5 Programming Documentation

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p06tcw6zjh3vm6n19vpkj0mxs5gi.htm

Koen

[Tom]

Looks to me like you just want to do a simple MERGE.

data join1;
  merge d1 d2;
  by x1-x4 ;
run;

[PeterClemmensen]

If you do not need to use SQL : 

data d1;
do i=1 to 1000;
  x1=rand('binomial',.1,1);
  x2=rand('binomial',.3,1);
  x3=rand('binomial',.6,1);  x4=rand('binomial',.8,1);
  y=x1+x2+x3+x4;
output;
end;
run;

data d2;
input x1 x2 x3 x4;
cards;
0 0 0 0
;
run;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "d2(keep = x:)");
	  h.definekey(all : "Y");
	  h.definedone();
   end;

   set d1;

   if h.check() = 0;

run;

[Ksharp]

NATRUAL JOIN could save your time .

proc sql;
create table join1 as
select a.*, b.*
from d1 a natural inner join  d1 b
;
quit;