DATA Step, Macro, Functions and more

BASE SAS

Reply
Contributor
Posts: 26

BASE SAS

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

 

Super User
Posts: 17,749

Re: BASE SAS

Look at Dictionary.Column

or sashelp.vcolumn

 

They're the same table. 

SAS Super FREQ
Posts: 3,475

Re: BASE SAS

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;

 

Super User
Posts: 5,071

Re: BASE SAS

[ Edited ]

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.

Super User
Posts: 6,928

Re: BASE SAS

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 26

Re: BASE SAS

[ Edited ]

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;

Valued Guide
Posts: 2,174

Re: BASE SAS

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

Super User
Posts: 17,749

Re: BASE SAS

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

Super User
Posts: 5,254

Re: BASE SAS

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
Ask a Question
Discussion stats
  • 8 replies
  • 322 views
  • 4 likes
  • 7 in conversation