topic Re: Matching all records in data1 to one row dataset data2 on variable list x1-x&k in SAS Enterprise Guide

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

RobertWF1 — Sat, 01 Jan 2022 17:20:20 GMT

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!

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

sbxkoenk — Sat, 01 Jan 2022 17:44:30 GMT

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

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

RobertWF1 — Sat, 01 Jan 2022 18:25:47 GMT

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?

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

Tom — Sat, 01 Jan 2022 18:32:21 GMT

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

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

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

PeterClemmensen — Sat, 01 Jan 2022 18:38:54 GMT

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;

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

sbxkoenk — Sat, 01 Jan 2022 20:14:22 GMT

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

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

Ksharp — Sun, 02 Jan 2022 10:27:44 GMT

NATRUAL JOIN could save your time .

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