topic Re: BASE SAS in SAS Programming

BASE SAS

sdixit — Thu, 04 Aug 2016 18:07:59 GMT

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

Re: BASE SAS

Reeza — Thu, 04 Aug 2016 18:24:11 GMT

Look at Dictionary.Column

or sashelp.vcolumn

They're the same table.

Re: BASE SAS

Rick_SAS — Thu, 04 Aug 2016 18:37:45 GMT

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;

Re: BASE SAS

Astounding — Thu, 04 Aug 2016 18:43:21 GMT

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.

Re: BASE SAS

Kurt_Bremser — Fri, 05 Aug 2016 06:49:32 GMT

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.

Re: BASE SAS

sdixit — Fri, 19 Aug 2016 19:14:11 GMT

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;

Re: BASE SAS

Peter_C — Fri, 19 Aug 2016 19:30:43 GMT

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

Re: BASE SAS

Reeza — Fri, 19 Aug 2016 19:48:45 GMT

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

Re: BASE SAS

LinusH — Sat, 20 Aug 2016 07:27:30 GMT

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.