DATA Step, Macro, Functions and more

Reading from SASHELP.VTABLE is taking Ages

Reply
Frequent Contributor
Posts: 107

Reading from SASHELP.VTABLE is taking Ages

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.

 

 

 

 

Super Contributor
Posts: 490

Re: Reading from SASHELP.VTABLE is taking Ages

[ Edited ]

Try using Proc SQL instead 

 

proc sql;
   create table mytable as
      select * from sashelp.vtable
      where libname='WORK';
quit;
Frequent Contributor
Posts: 107

Re: Reading from SASHELP.VTABLE is taking Ages

Thanks zaki :-)
Super Contributor
Posts: 490

Re: Reading from SASHELP.VTABLE is taking Ages

[ Edited ]

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

 

Super User
Super User
Posts: 7,401

Re: Reading from SASHELP.VTABLE is taking Ages

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;
Frequent Contributor
Posts: 107

Re: Reading from SASHELP.VTABLE is taking Ages

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.

Super User
Super User
Posts: 7,401

Re: Reading from SASHELP.VTABLE is taking Ages

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.

Frequent Contributor
Posts: 107

Re: Reading from SASHELP.VTABLE is taking Ages

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

Super User
Super User
Posts: 7,401

Re: Reading from SASHELP.VTABLE is taking Ages

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.

Super Contributor
Posts: 490

Re: Reading from SASHELP.VTABLE is taking Ages

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.

Super User
Posts: 9,676

Re: Reading from SASHELP.VTABLE is taking Ages

[ Edited ]

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;
Occasional Contributor FK
Occasional Contributor
Posts: 5

Re: Reading from SASHELP.VTABLE is taking Ages

Hi All,

 

I encounter the same issue:

 

Vtable_takes_ages.JPG

 

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?

Super User
Posts: 9,676

Re: Reading from SASHELP.VTABLE is taking Ages

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 ?


Super User
Super User
Posts: 7,401

Re: Reading from SASHELP.VTABLE is taking Ages

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.

Occasional Contributor FK
Occasional Contributor
Posts: 5

Re: Reading from SASHELP.VTABLE is taking Ages

Thank's for your suggestions.

 

@RW9  I'm afraid, we do need theses 640k tables.... :-)

 

 

 

Ask a Question
Discussion stats
  • 15 replies
  • 528 views
  • 1 like
  • 5 in conversation