Finding a specific variable into any libraries and dataset

[alepage]

Good morning,

I am working for an organization that has little information on datasets and libraries as well.

I am looking a fast way to localize a specific variable in any dataset and any libraries.

Does someone has ever work on this problem?

Regards,

Alain

[Kurt_Bremser]

If you can be sure that all directories that contain .sas7bdat files are assigned a library in your current session, then looking for your variable in dictionary.columns should do it.

If not, then you will have to make a file search for .sas7bdat files, convert the result to a dataset, and either run proc contents for all datasets that are not in an assigned library, or assign temporary libraries so that you can use dictionary.columns. I know that sometime in the past few months I posted an example for something similar here.

[ballardw · Accepted solution]

This would find all instances of a variable with the name you specify in data sets that are assigned to libraries. The upcase is used because the variable name that holds the variable name may be of mixed case. So use Upcase on the variable and provide an all upper case version of the variable you are searching for.

proc sql;
   create table variableinfo as
   select * 
   from dictionary.columns
   where upcase(name)='YOURVARIABLE'
   ;
quit;

If you are looking for a few variables then use this where

where upcase(name) in ('YOURVARIABLE' 'OTHERVAR' 'PDQ')

If you have many variables of interest it may be best to place them into a data set, one variable per record and use something like

proc sql;
   create table variableinfo as
   select b.* 
   from variablenameset as a
        left join
        dictionary.columns as b
        on upcase(a.vname) = upcase(b.name)
   ;
quit;

But this approach relies on the datasets being in currently define libraries.