finding specific values or string in a library with multiple datasets

[buddha_d]

SAS gurus,

                I am trying to find accountno in a library with multiple datasets. I am unable to accomplish the task and I have put the code I used to build the logic.  Can someone please show me the direction?

1. i am getting all the accountno total in a macro as well accountno

2. get all the datasets I need to look into using dictionary.columns

3. use a macro to list individual dataset which contains either one or more accountno in them. If possible need to get all the accountno information (possibly using proc sql; select option instead of data _null_ in my code). I would like ideally to have the information of the dataset and all the observations pertaining to that accountno. I am failing in my third step. please help?

thanks,

/*get the total number of accounts from kumngo and save them in a macro accountno*/
proc sql;
select count(distinct(accountno)) into :tot
from kumngo;
select distinct "'"||strip(accountno)|| "'" into :accountno

         seperated by ","
from kumngo;
quit;

/*Get all the the dataset names from datasets using dicitionary.columns*/
PROC SQL NOPRINT;
SELECT DISTINCT(MEMNAME), COUNT(DISTINCT(MEMNAME))
INTO :ds_list SEPARATED BY '~', :ds_cnt
FROM dictionary.columns
WHERE LIBNAME = 'DATASETS'
ORDER BY MEMNAME;
create table datasets as
select distinct (memname)
from dictionary.tables
where libname = 'DATASETS'
QUIT;

/*search individual dataset to find the accountno and list that dataset*/
%macro check;
%if &sqlobs ne 0 %then %do;
%DO I = 1 %TO &DS_CNT;
%LET DS = %SCAN (&DS_LIST., &I., ~);
data _null_;
set datasets.&DS. ;
if accountno in (&accountno) then put" accountnos are in &DS.";
run;

%end;
%end;
%mend;

%check ;

[ChrisNZ · Accepted solution]

Something like this would be a lot faster.

proc sql noprint;
  select 'DATASETS.'||MEMNAME into :dslist separated by ' '
  from DICTIONARY.TABLES
  where LIBNAME = 'DATASETS';
quit;

data WANT;
  set &dslist.(keep=ACCOUNTNO) indsname=_DS;
  where ACCOUNTNO in (&accountno.); 
  DSNAME=_DS;
run;

Please modify for your needs.

[buddha_d]

Thanks ChrisNZ. This seems to be the right solution. I would let you if I come across any problems. I didn't know indsname= option exists with set option.

Thanks,