topic Re: Importing Excel file from PC File Server - diff. CPU and real time in SAS Enterprise Guide

Importing Excel file from PC File Server - diff. CPU and real time

kdp — Mon, 24 May 2010 14:59:12 GMT

Hi,

I am using E-Guide 4.1 to import an Excel file from the PC File Server. However, there is a big gap between total CPU time (7 secs) and real time (11 mins) to import the file. I have tried various methods to import using code, but can't seem to reduce the time.

Here are the various methods I have tried and the logs:
1) Set Excel file as libname and use data step

libname db1 pcfiles server="wm-flor-ds052"
path="D:\SAS\PCFile\IE\LaborDemand\CIF_Hierarchy.xls";

options fullstimer;

data cif_stf2;
set db1.'depvar_list$'n;
if dep_var2 ^= "";
run;

NOTE: There were 17576 observations read from the data set DB1.'depvar_list$'n.
NOTE: The data set WORK.CIF_STF2 has 6040 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 9:54.00
user cpu time 1.96 seconds
system cpu time 4.78 seconds
Memory 771k
Page Faults 3
Page Reclaims 324
Page Swaps 0
Voluntary Context Switches 676347
Involuntary Context Switches 4913
Block Input Operations 0
Block Output Operations 0

2) Set Excel file as libname and use proc import code

proc import dbms=excelcs out=cif_stf (where=(dep_var2 ^= ""))
datafile="D:\SAS\PCFile\IE\LaborDemand\CIF_Hierarchy.xls" replace;

server="wm-flor-ds052";
sheet='depvar_list$'n;
run;

NOTE: .CIF_STF was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 11:14.00
user cpu time 2.09 seconds
system cpu time 4.91 seconds
Memory 240k
Page Faults 8
Page Reclaims 585
Page Swaps 0
Voluntary Context Switches 682592
Involuntary Context Switches 1664
Block Input Operations 0
Block Output Operations 0

3) Using File-->Import--> SAS Server--> libname - produces similar results as above

The only way it works: File-->Import-->Local Computer-->Excel file - this runs in 6 secs real time.

How can I replicate the above result using code either through method 1) or 2)?

Thanks,
kdp

Re: Importing Excel file from PC File Server - diff. CPU and real time

ChrisHemedinger — Tue, 25 May 2010 12:35:56 GMT

The "fast" method you cite is actually EG reading your Excel file and turning it into a SAS data set. There isn't a SAS program at work here.

You're using the PC Files server, which means that the Excel file has to be moved to another machine to be processed and that's where the work is done. The data set that results is sent back to SAS. So I suppose that your SAS session CPU is not heavily taxed; most of the time is spent waiting for data to be transferred to/from the PC Files server.

Chris

Re: Importing Excel file from PC File Server - diff. CPU and real time

kdp — Tue, 25 May 2010 13:55:51 GMT

So, is there some setting or parameters I can change in method 1) or 2) to make it run faster?

The file only has 17K rows with 15 columns, so it shouldn't take 12mins to import...that's a piece of cake for anything inside of SAS.

Re: Importing Excel file from PC File Server - diff. CPU and real time

SASKiwi — Wed, 02 Jun 2010 02:05:39 GMT

As Chris has suggested, it is possibly a slow, constrained network that causes this. Do you consistently get around the same time or is it faster outside usual work hours?

I suggest you convert the spreadsheet to a comma or tab delimited file (FILE SAVE AS in Excel) and try reading it in as a delimited file not a spreadsheet. Then compare the elapsed time.