topic Re: Reading from SASHELP.VTABLE is taking Ages in SAS Programming

Reading from SASHELP.VTABLE is taking Ages

KiranMaddi — Mon, 04 Jan 2016 09:49:42 GMT

Hello All,

I am running a Proc tabulate off the sashelp.vtable and is taking like 2 hours to run a simple proc tabulate.

Here is my code:

proc tabulate data= SASHELP.VTABLE missing out= Aquote_DataVolume (drop= _table_ _type_ _page_);
class libname;
var nobs;
table libname, nobs*sum;
where libname = 'QTEDATA';
run;

I have never use to have this problem before with the VTABLE. Programs using VTABLE use to run quickly. I am clueless to what is happening.

I have also tried a simple data step which is also taking ages.

data test;
set SASHELP.VTABLE;
where libname = 'work';
run;

Please can some one of you suggest me a solution for this?

Thanks in advance.

Re: Reading from SASHELP.VTABLE is taking Ages

mohamed_zaki — Mon, 04 Jan 2016 10:12:48 GMT

Try using Proc SQL instead

proc sql;
   create table mytable as
      select * from sashelp.vtable
      where libname='WORK';
quit;

Re: Reading from SASHELP.VTABLE is taking Ages

RW9 — Mon, 04 Jan 2016 10:16:11 GMT

Well, a few things here. Firstly your second code will not work, 'WORK' has to be in upper case. Secondly, if your running 9.4 then a lot of new tables were added into Base SAS - maps - and this has noticeably slowed these tables down, so filtering is advised. Thirdly, its probably not a good idea to run tabulate and other procedures directly on a View - which is what the V in VTABLE stands for. Have a datastep which creates a dataset with the required information, then tabulate off that. Finally, and I may not understand as I dont use tabulate, what is it your trying to do here? If you just want a sum then this will be faster:

proc sql;
  select sum(NOBS)
  from   DICTIONARY.TABLES
  where  LIBNAME="WORK";
quit;

Re: Reading from SASHELP.VTABLE is taking Ages

KiranMaddi — Mon, 04 Jan 2016 11:10:40 GMT

Thanks for your reply. Yes I just want to sum it. I have also realised that vtable is like a SQL view which can be access quickly by using PROC SQL. I just wonder why I have not encountered with this issue till now? Having been using the same program for about an year.

Re: Reading from SASHELP.VTABLE is taking Ages

KiranMaddi — Mon, 04 Jan 2016 11:11:08 GMT

Thanks zaki 🙂

Re: Reading from SASHELP.VTABLE is taking Ages

mohamed_zaki — Mon, 04 Jan 2016 11:46:38 GMT

You can read more here:

Accessing SAS System Information By Using DICTIONARY Tables

especially "DICTIONARY Tables and Performance" section.

And that was the same for 9.2 too

9.2 : Accessing SAS System Information by Using DICTIONARY Tables

Re: Reading from SASHELP.VTABLE is taking Ages

RW9 — Mon, 04 Jan 2016 11:27:12 GMT

Have you changed version of SAS? I note that there are lots of map tables added to the Base installation on 9.4, this means more records to process.

Re: Reading from SASHELP.VTABLE is taking Ages

KiranMaddi — Mon, 04 Jan 2016 11:30:13 GMT

No I have not changed the SAS version. Still using 9.3.

Re: Reading from SASHELP.VTABLE is taking Ages

RW9 — Mon, 04 Jan 2016 11:46:24 GMT

I would probably check with your IT on the setup then if nothing has changed, could be a glitch in network, something stuck somewhere etc.

Re: Reading from SASHELP.VTABLE is taking Ages

mohamed_zaki — Mon, 04 Jan 2016 11:52:07 GMT

Maybe you just have more data and tables now by the time. And as in SAS documentations

When you query DICTIONARY.TABLES or SASHELP.VTABLE, all the tables and views in all the libraries that are assigned to the SAS session are opened to retrieve the requested information.

so now it is harder for Data step to handle it and as PROC SQL is more optimized for that it can do it faster.

Re: Reading from SASHELP.VTABLE is taking Ages

Ksharp — Tue, 05 Jan 2016 06:49:21 GMT

There must be some LIBNAME set up for outside DataBase like ORACLE DB2 , which would cose you lots of time to build a connection with outside DataBase . Check " Engine Name " this field in DICTIONARY.LIBNAMES and see if there are some special engine other that V9 .

select * from dictionary.libnames;

Re: Reading from SASHELP.VTABLE is taking Ages

FK — Tue, 26 Jul 2016 07:09:33 GMT

Hi All,

I encounter the same issue:

more than 4 hours for about 640k rows.....!

It appears that parts of it are due to a slow network connection to the sas server since there is a difference of approximately 30 min between "real time" and "cpu time".....

Any ideas?

Re: Reading from SASHELP.VTABLE is taking Ages

Ksharp — Tue, 26 Jul 2016 08:04:51 GMT

You could query MEMBRES dictionary to get the table name, that would be very fast.

select * from dictionary.members;


After that use the proc contents to get all the variables name:

proc contents library=work._all_;
run;



 IML code also can get it. You want IML code ?

Re: Reading from SASHELP.VTABLE is taking Ages

RW9 — Tue, 26 Jul 2016 08:14:07 GMT

Personally I would take it up with your IT team, and possibly your SAS contact to get the speed resolved otherwise it will afffect anything you do - if of course it is a network speed issue. Also I would check why you have 640k rows in SASHELP.VTABLE - that is 640k tables you have referenced - are you really working with that many files all the time? It sounds to me like your setup is incorrect, re-assess how SAS starts up, what libraries and files each session actually needs - maybe have an autoexec for different studies or workflows, I mean even at maximum I have no more than 100 tables referenced at any working time.

Re: Reading from SASHELP.VTABLE is taking Ages

FK — Tue, 26 Jul 2016 12:30:42 GMT

Thank's for your suggestions.

@RW9 I'm afraid, we do need theses 640k tables.... 🙂

Re: Reading from SASHELP.VTABLE is taking Ages

RW9 — Tue, 26 Jul 2016 12:46:07 GMT

I am not suggesting removing the datasets, only the references to them. For instance, if I point a libname to abc and in there there is 30 datasets, and a libname to def which has another 40 datasets, sashelp.vtable will have standard ones + these 70 observations - SAS keeps metadata about all items in the references. I am only working on data from def, it makes no sense to have a libname pointing to abc, just a waste of resources. This is called your personal setup. There are many ways to do it, in one case, if I am working on one study I will have a setup file to setup libanes for that one study - I need not references to any study every made.

Condense the user work setup to what is needed by that user at that time, rather than just having one setup which points to everything on your system.