01-04-2016 04:49 AM
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_);
table libname, nobs*sum;
where libname = 'QTEDATA';
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.
where libname = 'work';
Please can some one of you suggest me a solution for this?
Thanks in advance.
01-04-2016 05:10 AM - edited 01-04-2016 05:12 AM
Try using Proc SQL instead
proc sql; create table mytable as select * from sashelp.vtable where libname='WORK'; quit;
01-04-2016 06:21 AM - edited 01-04-2016 06:46 AM
You can read more here:
especially "DICTIONARY Tables and Performance" section.
And that was the same for 9.2 too
01-04-2016 05:16 AM
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;
01-04-2016 06:10 AM
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.
01-04-2016 06:27 AM
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.
01-04-2016 06:52 AM
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.
01-05-2016 01:47 AM - edited 01-05-2016 01:49 AM
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;
07-26-2016 03:09 AM
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".....
07-26-2016 04:04 AM
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 ?
07-26-2016 04:14 AM
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.