topic Re: query a libname for variable and contents in SAS Programming

query a libname for variable and contents

Q1983 — Sun, 28 Mar 2021 18:57:31 GMT

proc sql;
create table columns as
select name as variable
,memname as table_name
from dictionary.columns
where libname = 'LAKEP'
/*and user_id in ('1LB','402','CM7','EI9','JS2','J8M',
'KS7','MB2','NR4','VG5') or username
in ('1LB','402','CM7','EI9','JS2','J8M',
'KS7','MB2','NR4','VG5')*/
;
quit;

The above code will return the variable columns for a particular libname. I know you cannot run this code however as a general question, is it possible to do a query of an entire libname and return the contents of a particular variable? As you can I commented out user_id and username. i am trying to determine If I can isolate particular user_id. Currently I am performing dataset queries on each individual table.

Re: query a libname for variable and contents

ballardw — Sun, 28 Mar 2021 20:27:02 GMT

Generally querying each set is going to be the approach. If you are looking for a way to reduce the code needed then

Put the list of data sets that actually have variable named user_id such as

proc sql;
   create table temp as
   select libname, memname 
  from dictionary.columns
  where libname = 'LAKEP'
  and upcase(name)='USER_ID'
  ;
quit;

Use that data set to select the records you want such as with Call execute

data _null_;
    set temp;
    call execute ("data work.memname;")
    call execute ("set "||catx('.',libname,memname)||";");
    call execute ("where user_id in (<your list of values goes here>);";
    call execute ("run;")
run;

I'm taking a short way out using work.memname. You could use the values of Memname and any string functions you want ot create a different data set name.

Re: query a libname for variable and contents

SASKiwi — Sun, 28 Mar 2021 20:46:40 GMT

You can use the COLUMNS Dictionary table to find what tables user_id and username are in, then you could use the FREQ procedure to profile the distinct values in these columns in each of those tables. Here is an example of this approach:

proc sql;
create table tables as
select  libname
       ,memname 
from dictionary.columns
where libname = 'SASHELP'
and name = 'NAME';
quit;

data _null_;
  set tables;
  call execute('proc freq data = ' !! libname !! '.' !! memname !! ' (obs = 20); table name; run;');
run;

Re: query a libname for variable and contents

Patrick — Sun, 28 Mar 2021 23:38:51 GMT

If you can be sure that the column attributes (especially type and length) are the same in all source ds then you could also do something like below (code not tested) in case you want to create data which you can report on in a single report.

proc sql noprint;
select cats(libname,'.',memname,'(keep=',name,')') into :ds_list separated by ' '
,memname as table_name
from dictionary.columns
where libname = 'LAKEP'
/*and user_id in ('1LB','402','CM7','EI9','JS2','J8M',
'KS7','MB2','NR4','VG5') or username
in ('1LB','402','CM7','EI9','JS2','J8M',
'KS7','MB2','NR4','VG5')*/
;
quit;

data all;
  length _source_ds sourc_ds $41;
  set &ds_list inds=_source_ds;
  source_ds=_source_ds;
run;