BookmarkSubscribeRSS Feed
sdixit
Obsidian | Level 7

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

 

8 REPLIES 8
Reeza
Super User

Look at Dictionary.Column

or sashelp.vcolumn

 

They're the same table. 

Rick_SAS
SAS Super FREQ

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;

 

Astounding
PROC Star

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.

Kurt_Bremser
Super User

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.

sdixit
Obsidian | Level 7

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;

Peter_C
Rhodochrosite | Level 12

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")

Reeza
Super User

If it's too slow look at getting the data from proc datasets again and filter that. 

LinusH
Tourmaline | Level 20
How long is long? What's your application?
No of tables/columns can affect performance as well as libname engine, options and i/o, network constraints.
Data never sleeps

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!

How to Concatenate Values

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.

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
  • 1307 views
  • 4 likes
  • 7 in conversation