topic Re: Column name search in SAS Enterprise Guide

Column name search

paul2877 — Fri, 16 Dec 2016 02:41:20 GMT

I have a schema with over 100 tables. I would like to find a column named for example "Teams" and list all the tables where it finds the column name Teams. Can someone help?

Re: Column name search

Ksharp — Fri, 16 Dec 2016 03:20:25 GMT

This could give you all the column names.

proc sql;
connect to odbc(....);
create table want as
 select * from connection to odbc(ODBC::SQLColumns);
quit;

Re: Column name search

paul2877 — Fri, 16 Dec 2016 04:26:59 GMT

I know what the column is.. i want to know which tables they are in?

Re: Column name search

Reeza — Fri, 16 Dec 2016 04:43:15 GMT

Most DBs have a dictionary table that contains table and column names.

If your datasets sets are SAS data sets, check SASHELP.vcolumn to find all columns named Team.

If your dataset are from a DB they may not be listed in SASHELP but the DB should have its own dictionary.

Re: Column name search

Ksharp — Fri, 16 Dec 2016 04:48:51 GMT

It is simple if you already have a connection to DB.


libname xx odbc dsn=..............;

proc sql;
select *
 from dictionary.columns
  where libname='XX' and name='col_name';
quit;

Re: Column name search

Reeza — Fri, 16 Dec 2016 06:10:12 GMT

In addition to @Ksharp code, check the case of the values in the Dictionary table or UPCASE everything.

UPPER()/UPCASE() can be used.

Re: Column name search

JJP1 — Tue, 30 Jul 2019 10:50:31 GMT

HI @Reeza ,

Iam running below query to get to know the column age is present in which dataset under sashelp library ?.
but iam not getting any result in output.even though i can see age column is available.kindly help please.

proc sql;
select * from DICTIONARY.COLUMNS
where LIBNAME = 'SASHELP' and NAME = 'AGE'
;
quit;

Re: Column name search

jebjur — Tue, 30 Jul 2019 13:47:05 GMT

Make sure you use the UPCASE function, as some column names are lowercase:

proc sql;
select * from DICTIONARY.COLUMNS
where LIBNAME = 'SASHELP' and UPCASE(NAME)= 'AGE';
quit;

Re: Column name search

JJP1 — Tue, 30 Jul 2019 14:08:02 GMT

WOW.Thanks Jebjur for helping me