Joining multiple sas files in a folder within a loop?

[Frank_johannes]

Hi All, 

I have a SAS file containing a unique ID-

I want to join this ID from table ID  with multiple sas files in a folder to create a new table showing me which tables each ID exist in? 

Is there a smart way to do this instead of writing each join in a separate SAS query?

proc SQL; 

create table want as 

select A.*, B.* 

from ID a 

left join week_10 B

on A.ID = B.ID 

; 

quit;

Kind regards 

Frank 

[PaigeMiller · Accepted solution]

There is no looping possible in PROC SQL, unless you write a macro.

I want to join this ID from table ID  with multiple sas files in a folder to create a new table showing me which tables each ID exist in? 

This can be done in a SAS data step, with the SET command (and no loop needed here).

data want;
    set /* your list of data set names go here */ indsname=indsname;
    dsname = indsname;
    keep dsname id;
run;

[ballardw]

A basic report for which ID values are in which table:

proc tabulate data=want;
   class dsname id;
   table id,
           dsname=' '*n=' '
           /misstext=' '
   ;
run;

Would show row with the value of ID and column for data set name and a count of how many times the value occurs in the table as the body of the table. If you are likely to have more tables than ID values then reverse the positions of ID and Dsname in the table statement of the Proc Tabulate example.

[Tom]

Do you want to count how many observations? Or just get an 1/0 (BOOLEAN) flag for existence or not?

Do you have multiple IDs you want to check?

Or do you want to check ALL of the IDs?

If you want to generate a series of boolean flags for ALL of the IDS then something like this probably the simplest.

data want;
  merge a(keep=id in=in1) b(keep=id in=in2) .... z(keep=id in=in26) ;
  by id;
  if first.id;
  a=in1;
  b=in2;
  ...
  z=in26;
run;

Which is simple enough to generate from a list of datasets.

proc contents data=mylib._all_ out=contents noprint;
run;

data names;
  set contents;
  where upcase(name)='ID';
   by memname;
   if first.memname;
   dsnum+1;
   keep libname memname dsnum;
run;

filename code temp;
data _null_;
  file code ;
  put 'merge' ;
  do while(not eof1);
    set names end=eof1;
    put libname +(-1) '.' memname '(keep=id in=in' dsnum ')' ;
  end;
  put ';' / 'by id;' / 'if first.id;' ;
  do while(not eof1);
    set names end=eof2;
    put memname '=in' dsnum ';' ;
  end;
  put 'run;' ;
  stop;
run;
%include code /source2;