Searching for value across datasets in library

[Helle]

Hi,

Is it possible to search for a specific value across all datasets in a library? I have a list of incorrect userids and I would like to know if these are present in any other datasets in a specific library.

I have tried a lot of different search combinations in Google to find something about this topic but haven't had much luck so hopefully somebody in this forum can shed some light on this for me.

Thanks a lot,

Helle

[data_null__]

What version of SAS are you running?

[Doc_Duke]

Asking this group "is it possible" gets an obvious yes. More importantly, is it easy, straightforward, etc. However, you need to provide more information.

Are the datasets SAS datasets?
Are the value you are looking for the only thing in a field?
Is the field always the same?
Is the value in a free text field?
As the previous respondent asked, what version of SAS. They added some functionality since 8.2.

[data_null__]

Using the Doctors questions as a guide.

> Are the datasets SAS datasets?
Yes
> Are the value you are looking for the only thing in a
> field?
Yes.
> Is the field always the same?
Yes.
> Is the value in a free text field?
No
> As the previous respondent asked, what version of
> SAS. They added some functionality since 8.2.
Ver 9.1.3

NOTE: I attempted to make this example self contained. The program creates a data library SDF in WORK and then deletes it. Don't be distracted by that part of the example.

[pre]
/*create library in WORK*/
/*you don't need this example only*/
data _null_;
length ddname $8 path sdf $256;
path = catx('\',pathname('WORK','L'),'SDF');
rc1 = filename(ddname,path);
sdf = dcreate('SDF',pathname('WORK'));
rc2 = libname('SDF',sdf);
put 'NOTE: ' (_all_)(=);
run;

libname SDF list;
/* create sample data */
data sdf.class1(where=(sex='F')) sdf.class2 sdf.class3(where=(sex='M')) sdf.class4;
set sashelp.class;
run;

/* list of bad IDs */
proc format;
invalue bad(upcase just default=40) 'JOHN','MARY'=1 other=0;
run;


/* gen some code */
proc sql;
select
cats(catx('.',libname,memname),'(keep=name in=in',monotonic(),')'),
catx(' ',cats('when(in',monotonic()),') inds=',quote(catx('.',libname,memname)),';')
/* see http://support.sas.com/kb/34/513.html for the 9.2 easy way to do this*/
into
:statement1 separated by ' ',
:statement2 separated by ' '

from dictionary.members
where libname eq 'SDF' and memtype eq 'DATA'
;
quit;
run;



options symbolgen=1;
data badID;
set &statement1 open=defer;
/* open=defer will be good only if the variable of interest(NAME) has */
/* same type and length for all library members*/
if input(name,bad.) eq 1;
length inds $41;
select;
&statement2
end;
run;
options symbolgen=0;

title 'List of BAD IDS and SAS data set where located';
proc print;
by notsorted inds;
id inds;
run;


/* Remove the example library SDF */
/* use for example only */
proc datasets kill library=sdf;
quit;
data _null_;
length ddname $8 path $256;
path = pathname('SDF','L');
rc1 = filename(ddname,path);
rc2 = fdelete(ddname);
rc3 = libname('SDF');
put 'NOTE: ' (_all_)(=);
run;
[/pre]

[Helle]

Hi,

Thanks for your suggestion which I can definitely adapt to my needs. In the ideal world, it would be great to be able to search all datasets regardless of variable name, length etc. in a Google-like manner but in the meantime this will do.

We are running 9.1.3 by the way and the other questions were answered correctly, too 🙂

Thanks,

Helle