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.
Try using Proc SQL instead
proc sql;
create table mytable as
select * from sashelp.vtable
where libname='WORK';
quit;
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
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;
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.
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.
No I have not changed the SAS version. Still using 9.3.
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.
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.
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;
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?
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 ?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.