How do I search multiple tables output when find condition

[cad88]

Hello,

I have a library that contains tables that start with o_ and some that don't. I want to be able to search through the o_ tables for a certain ID, such as where ID = 12345. I then would like to have separate outputs with the rows where the ID is found for each table.  The tables are very different in structure so cannot be stacked.  The ID field is named the same in each table.

For example

Need it so when ID12345 is found in tables A,C,D then I get separate outputs for A,C,D with all the rows that ID12345 is in. Next person I lookup could be in tables B,E,F, so I would need separate outputs for B,E,F where that person is found. The number of tables grow each year so I do not want to maintain a list of the tables, just know that they all have the same naming convention. I need all the fields in the dataset to output and not just ID.

(Version 8.2)



Thanks!

[Reeza]

If you're only filtering on ID why does it matter? Does this get you close?

data want;
set lib.o_: indsname = source;
where id = 12345;
record = _n_;
file = source;

Keep ID;
run;

[cad88]

Need it so when ID12345 is found in tables A,C,D then I get separate outputs for A,C,D with all the rows that ID12345 is in.  Next person I lookup could be in tables B,E,F, so I would need separate outputs for B,E,F  where that person is found.  The number of tables grow each year so I do not want to maintain a list of the tables, just know that they all have the same naming convention.

[mkeintz]

data o_girls   o_boys;
  set sashelp.class;
  if sex='F' then output o_girls;
  else output o_boys;
run;

data recombined;
  set o_: ;
  where name='John';
run;

[ballardw]

Look in this thread: https://communities.sas.com/t5/SAS-Programming/How-to-set-all-datasets-from-library/m-p/734182

Has an example like yours that looks for "visit" instead of "ID".

The example first builds a data set of nothing but the "visit" values and the dataset identification.

Caveats: This will only work if your variable is of the same type in all data sets.

Second, if other common named variables in different datasets are of different types you will need to modify the code keep just the ID variable from each data set using something like

  call execute(NAME || "(keep = id)" );

Call execute places code in a buffer to execute after the end of the data step using it.

[cad88]

Looking to have a separate output for each table with all the rows and fields.

[jebjur · Accepted solution]

Something similar to this code may work:

proc sql noprint;
select count(distinct memname) into :cnt from dictionary.columns
where libname = "WORK" and substr(upcase(memname),1,2) = "_O";

select distinct catx(".",libname,memname), catx(".",libname,trim(memname)||'_out')
into :dsnames1 - :dsnames%trim(%left(&cnt)),:dsout1 - :dsout%trim(%left(&cnt))
from dictionary.columns
where libname = "WORK" and substr(upcase(memname),1,2) = "_O";
quit;

%macro split;
%do i=1 %to &cnt;
data &&dsout&i;
set &&dsnames&i;
where ID=12345;
run;
%end;

%mend;

%split;

[cad88]

Great! Thank You! It did output an out table for all tables even if member was not in them but that was an easy fix.

[Tom]

So you want to do something like:

data work.o_a;
  set mylib.o_a;
  where ID = 12345;
run;

data work.o_b;
  set mylib.o_b;
  where ID = 12345;
run;

...

If so then all you need to do is get the list of dataset names.

proc sql noprint;
  create table dslist as 
  select memname 
  from dictionary.columns
  where libname = 'MYLIB'
    and memname like 'O^_%' escape '^' 
    and upcase(name) = 'ID'
  ;
quit;

and use that to generate the code

filename code temp;
data _null_;
  set dslist ;
  file code ;
  put 
     'data work.' memname ';'
   / '  set mylib.' memname ';'
   / '  where ID = 12345;'
   / 'run;'
  ;
run;
%include code /source2;