BookmarkSubscribeRSS Feed
paul2877
Calcite | Level 5

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?

8 REPLIES 8
Ksharp
Super User
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;



paul2877
Calcite | Level 5

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

Ksharp
Super User
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;


Reeza
Super User

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

 

UPPER()/UPCASE() can be used. 

JJP1
Pyrite | Level 9

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;
jebjur
SAS Employee

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;

JJP1
Pyrite | Level 9
WOW.Thanks Jebjur for helping me
Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 9932 views
  • 1 like
  • 5 in conversation