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