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!
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
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
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
Looks to me like you just want to do a simple MERGE.
data join1;
merge d1 d2;
by x1-x4 ;
run;
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;
NATRUAL JOIN could save your time .
proc sql; create table join1 as select a.*, b.* from d1 a natural inner join d1 b ; quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.