topic Re: Joining multiple sas files in a folder within a loop? in SAS Programming

Joining multiple sas files in a folder within a loop?

Frank_johannes — Fri, 20 Jan 2023 13:23:07 GMT

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

Re: Joining multiple sas files in a folder within a loop?

PaigeMiller — Fri, 20 Jan 2023 13:58:31 GMT

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;

Re: Joining multiple sas files in a folder within a loop?

Tom — Fri, 20 Jan 2023 14:11:26 GMT

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;

Re: Joining multiple sas files in a folder within a loop?

ballardw — Fri, 20 Jan 2023 15:48:03 GMT

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.