topic Re: How do I search multiple tables output when find condition in SAS Enterprise Guide

How do I search multiple tables output when find condition

cad88 — Fri, 23 Apr 2021 22:15:42 GMT

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!

Re: How do I search multiple tables output when find condition

Reeza — Fri, 23 Apr 2021 21:24:00 GMT

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;

Re: How do I search multiple tables output when find condition

mkeintz — Fri, 23 Apr 2021 21:25:02 GMT

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;

Re: How do I search multiple tables output when find condition

cad88 — Fri, 23 Apr 2021 21:39:25 GMT

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.

Re: How do I search multiple tables output when find condition

ballardw — Fri, 23 Apr 2021 22:07:00 GMT

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.

Re: How do I search multiple tables output when find condition

cad88 — Tue, 27 Apr 2021 12:25:02 GMT

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

Re: How do I search multiple tables output when find condition

jebjur — Wed, 28 Apr 2021 14:17:58 GMT

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;

Re: How do I search multiple tables output when find condition

Tom — Wed, 28 Apr 2021 14:37:43 GMT

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;

Re: How do I search multiple tables output when find condition

cad88 — Thu, 06 May 2021 14:21:10 GMT

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.