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;
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;
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.
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.