topic Re: Reading from sashelp.vcolumn is taking too long in SAS Procedures

Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 13:23:47 GMT

I have a program that reads regularly from this table. The time taken to read data from it tends to vary, but it should only take a few seconds. When I proc print it to the screen, it's 7,000 observations or so. Proc printing it takes 55 seconds to run. Any proc or data step that accesses it takes 55 seconds or so on average, and just now it took 2½ minutes. Any idea why this might be? The PC is a slow one, but even 1GB of RAM and a 2.2GhZ processor should have no problems with this. There are other processes running in the background, but the only place in the program that hangs is where it accesses this table. I can't access my C drive on Windows Explorer, where the file is, so I can't tell if my C drive is short of space. 20434 proc print data = sashelp.vcolumn; 20435 where libname = 'WORK' and memtype = 'DATA' and substr(name,1,3) = 'VAR'; 20436 run; NOTE: No observations were selected from data set SASHELP.VCOLUMN. NOTE: There were 0 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='WORK') and (memtype='DATA') and (SUBSTR(name, 1, 3)='VAR'); NOTE: PROCEDURE PRINT used (Total process time): real time 2:30.42 cpu time 7.56 seconds

Re: Reading from sashelp.vcolumn is taking too long

RW9 — Tue, 22 Jul 2014 13:43:27 GMT

The SASHELP.VCOLUMN is actually an SQL view, so you need to factor that in. If you use it regularly then datastep to another area restricting on libname ne sashelp and then reference that dataset, save re-running each time.

Re: Reading from sashelp.vcolumn is taking too long

Astounding — Tue, 22 Jul 2014 13:52:03 GMT

A couple of alternative approaches that might run faster ...

Try using PROC SQL on dictionary.columns (instead of using sashelp.vcolumn).

Try running PROC CONTENTS using the options NOPRINT and OUT= to capture the contents of a particular SAS data set. Also note that you can capture information about all data sets in a library by specifying DATA=libname._all_.

Re: Reading from sashelp.vcolumn is taking too long

jakarman — Tue, 22 Jul 2014 14:11:01 GMT

Accessing the sashelp.vmember or dictionary.columns will result at runtime:

- query-ing of all active allocates libraries for existing tables

- for each table of that list query-ing on each table for the existing columns and their attributes.

All this queries are a lot of small pieces of information the system is not tuned and designed for. This same kind of performance burden can be seen opening a lot files with small data to the same size of data collected to be in some bigger files. There is a lot overhead accessing headers/metadata.

When a RDBMS is involved (libname) or a RLS (sas/connect) it go everytime round the circle as the information is not seen as a static table.

It is good example of very easy way of getting of information can cause expected bad performance.
Copying the sashelp.vcolums first to a work.tabel and than go for selecting can help improving your performance without losing the ease on usage.

Advice: only once using the sashelp.vcloumn further processing on a static copy.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 14:16:11 GMT

Here is how I am using sashelp.vcolumn. I'm unsure how to replace the references to it, such that it will work. Also, can you guys see the following code properly? It's all run together in my browser. I'm using Internet Explorer 7.

%Macro CreateRPTFile(OutputFile, Dataset, RecLength, Library=work); **This data step writes the variable names contained in the SAS dataset that is specified in the &Dataset macro variable, to the file specified in the OutputFile parameter; Filename Fname "&OutputFile"; Data _Null_; File Fname DLM = "," LRECL = &RecLength; Set sashelp.vcolumn; If _n_ = 1 Then Put "!," @; **For libname and memname to work, the names that feed into them must be in uppercase ; Where libname = Put("&Library",$upcase50.) and memname = Put("&Dataset",$upcase50.); Put name @; Run; **This datastep outputs all of the observations in the specified dataset to the external file; Data _Null_; File Fname DLM = "," LRECL = &RecLength Mod; Set &Library..&Dataset; put "*," (_all_) (:); run; %Mend;

Re: Reading from sashelp.vcolumn is taking too long

Quentin — Tue, 22 Jul 2014 14:18:10 GMT

Agree with Astounding's suggestion that PROC CONTENTS is often faster than hitting sashelp.vcolumn (or dictionary.columns).

Also, are you sure there are only 7,000 obs in sashelp.vcolumn? When I start PC SAS, I have more records than that, just from MAPS libraries and others. Remember, it has information on the variables for every dataset in every library.... it gets big quick. In BI setting with pre-assigned libraries, it can be enormous.

Another point is that using the SUBSTR() function on the where statement may mean that SAS can't use indexes to find the records more quickly.

Re: Reading from sashelp.vcolumn is taking too long

RW9 — Tue, 22 Jul 2014 14:21:27 GMT

Sorrry, I find it very difficult to read that code. Do you just want to print a dataset to a text file with a delimiter? Try proc export? If not maybe clarify what you want out as I don't see what you are trying to achieve, is it perhaps to check the existence before outputting - then use the exist() function.

Re: Reading from sashelp.vcolumn is taking too long

Tom — Tue, 22 Jul 2014 14:46:41 GMT

Here is your problem.

Where libname = Put("&Library",$upcase50.)

SAS will not be able to optimize this since it involves the use of a run-time function call to the PUT() function. So it will need to attempt to open every table in every active library.

Try changing it to:

Where libname = %upcase("&Library")

Now to regular SAS the where clause will contain a constant and SAS should be able to limit itself to just querying the specified library.

Re: Reading from sashelp.vcolumn is taking too long

Quentin — Tue, 22 Jul 2014 14:46:56 GMT

There are lots of ways to make a CSV file, but using your current macro as a starting point, below changes to use PROC CONTENTS to get the var list from the dataset, and PROC SQL to make a macro variable holding that list:

options mprint;

%Macro CreateRPTFile(OutputFile, Dataset, RecLength, Library=work);
%local varlist;

Filename Fname "&OutputFile"; 

proc contents data=&library..&dataset out=__varlist (keep=name) noprint;
run;

proc sql noprint;
  select distinct name into : varlist  separated by ',' 
  from __varlist 
  ;
quit;

**This datastep outputs all of the observations in the specified dataset to the external file; 
Data _Null_; 
  File Fname DLM = "," LRECL = &RecLength; 
  Set &Library..&Dataset;   
  If _n_ = 1 Then Put "!,&varlist";
  put "*," (_all_) (:);
run;

proc datasets library=work memtype=data nolist;
  delete __varlist;
run;
%Mend;


data test;
  array x{5};
  do id=1 to 5;
    do i=1 to dim(x);
      x{i}=ranuni(0);
    end;
    output;
  end;
  drop i;
run;

%CreateRptFile(d:/junk/test2.txt,test,100)

HTH,

--Q.

Re: Reading from sashelp.vcolumn is taking too long

data_null__ — Tue, 22 Jul 2014 14:50:55 GMT

You don't need sashelp.vcolumn or dictionary.columns or PROC CONTENTS for this task. You can use VNEXT to put the name row and in the same data step put the observations.

data _null_;  
   set SASHELP.class;  
   file log dlm='|' dsd;  
   if _n_ eq 1 then link names;  
   put (_all_)(:);  
   return;  
names:  
   length _name_ $32;  
   do while(1);  
      call vnext(_name_);  
      if upcase(_name_) eq '_NAME_' then leave;  
      put _name_ : @;  
      end;  
   put;  
   return;  
   run;  

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 15:28:58 GMT

Many thanks! This runs much more quickly. However, the variables are written to the output text file in alphabetical order. Is there an option on proc contents that will write the variables to __varlist in the order in which they were created in the input dataset?

Re: Reading from sashelp.vcolumn is taking too long

Tom — Tue, 22 Jul 2014 15:35:34 GMT

ORDER BY VARNUM

proc contents data=&library..&dataset out=__varlist (keep=name varnum) noprint;

run;

proc sql noprint;

select distinct name into : varlist separated by ','

from __varlist

order by varnum

;

quit;

Re: Reading from sashelp.vcolumn is taking too long

Quentin — Tue, 22 Jul 2014 15:41:30 GMT

You can add varnum to the KEEP= option when PROC CONTENTS outputs the dataset.

Then add ORDER BY VARNUM to the SQL step.

You can also remove DISTINCT from the SQL step, since you can't have two vars with same name it isn't needed.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:12:29 GMT

Quentin, on login this morning, I'm seeing 5,200 observations when I run proc print on sashelp.vcolumn. Yesterday evening I saw something over 7,000. Running this morning's proc print took 3:45!

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:15:42 GMT

Tom, I changed my code to use the %ucase function but the times doubled, approximately. This probably isn't anything to do with the change of function, it's probably other factors. When I logged on this morning, a proc print statement on sashelp.vcolumn took 3:45.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:19:11 GMT

Hi data _null_, how do you format your code when posting to the forum?

Re: Reading from sashelp.vcolumn is taking too long

jakarman — Wed, 23 Jul 2014 07:23:20 GMT

@mediaeval for the performance try to understand what you are causing in the system. See my previous post(s).

Re: Reading from sashelp.vcolumn is taking too long

RW9 — Wed, 23 Jul 2014 08:06:06 GMT

Is the code written like that in your SAS window, if so then format there and then copy to notepad and then to this window, also you can use the advanced editor top right of the edit window.

As for your slowdowns, could be any number of things. How is SAS installed, locally, network, virtual, what speeds are the networks running at, how much ram on the server etc. I would run through the executing process with your SAS/Network people to see why you are getting such speeds, its probably something simple, for instance we were getting terrible speeds sometime back, there was a syncronisation program running which was interfering with read/write on files.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 10:07:01 GMT

Thanks everyone for taking the time to answer. In the end I used Quentin's solution and runtimes have reduced from 10 minutes or so to just under 3 minutes.

Re: Reading from sashelp.vcolumn is taking too long

data_null__ — Wed, 23 Jul 2014 12:09:13 GMT

Do you see CEDA messages?

15379 - Cross-Environment Data Access (CEDA) utilization message

NOTE: Data file libref.member.DATA is in a format native to

another host or the file encoding does not match the

session encoding. Cross Environment Data Access will

be used, which may require additional CPU resources

and reduce performance.