BookmarkSubscribeRSS Feed
KiranMaddi
Obsidian | Level 7

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.

 

 

 

 

15 REPLIES 15
mohamed_zaki
Barite | Level 11

Try using Proc SQL instead 

 

proc sql;
   create table mytable as
      select * from sashelp.vtable
      where libname='WORK';
quit;
KiranMaddi
Obsidian | Level 7
Thanks zaki 🙂
mohamed_zaki
Barite | Level 11

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
KiranMaddi
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

KiranMaddi
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

mohamed_zaki
Barite | Level 11

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.

Ksharp
Super User

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;
FK
Calcite | Level 5 FK
Calcite | Level 5

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?

Ksharp
Super User
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 ?


RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

FK
Calcite | Level 5 FK
Calcite | Level 5

Thank's for your suggestions.

 

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

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 7833 views
  • 1 like
  • 5 in conversation