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?
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;
I know what the column is.. i want to know which tables they are in?
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;
In addition to @Ksharp code, check the case of the values in the Dictionary table or UPCASE everything.
UPPER()/UPCASE() can be used.
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;
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.