Hi All,
Is there any easy way to find where the list of variables present in the libarary.
Example :
I have list of variables ABC , BDC , XYZ...So on.
I would like to know which table contains these fields in library,
Thanks
Look at Dictionary.Column
or sashelp.vcolumn
They're the same table.
There are several ways to do this. One way is to use SASHELP dictionary views.
For example, the following code finds all data sets in the SASHELP library that contain a numeric variable named "date" or "name":
data FindVars;
set sashelp.vcolumn;
if libname="SASHELP" and Type="num" and
upcase(name) in ("NAME" "DATE");
run;
proc print; run;
Just to add a little context, since Reeza has already pointed you in the right direction:
libname mylib '/path/to/some/folder';
proc sql;
select memname, name from dictionary.columns where libname='MYLIB' and upcase(name) in ('ABC', 'XYZ');
quit;
Within dictionary.columns, the LIBNAME values are stored in uppercase, but the variable NAME values can be mixed case.
Other fields related to each variable are also contained in dictionary.columns and can be extracted similarly.
***** EDITED: Nothing wrong with Rick's suggestion either ... we were evidently typing at the same time.
Just an addendum for clarification:
dictionary.columns is available within proc sql.
sashelp.vcolumn is available outside of proc sql (for data and proc steps) and is in fact a view to dictionary.columns
Both are built/executed dynamically when they are used.
Hi,
I am trying to find PrinBal variable belongs to which table in MTGAE libaray but It is taking long time to run.
Can you help why?
Proc sql;
SELECT *
FROM DICTIONARY.COLUMNS
WHERE UPCASE(LIBNAME) in ("MTGAE") AND memtype ="DATA" And
NAME in ("PrinBal") ;
QUIT;
It is slow because you apply upcase() to the libname value.
Just use where clause
WHERE LIBNAME in ("MTGAE") AND memtype ="DATA" And
NAME in ("PrinBal")
If it's too slow look at getting the data from proc datasets again and filter that.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.