How to import excel with guessingrow function

[Andrea_Peng]

I know guessingrows is working when proc import csv but not excel. I need to import excel file with multiple sheet. Some variables may missing first 20 rows but get value later. How to write the code to let SAS scan all the values and put in the probable format?

my code is as follow: 

proc import datafile="C:\work\test.xlsx" out=test dbms=excel replace;
sheet="sheet1";
getnames=yes;
mixed=yes;
scantext=YES;
scantime=YES;
run;

[RW9]

Do you mean like this?

http://support.sas.com/kb/35/563.html

As Excel file formats do not support the guessingrows options.

Use a decent file format - CSV, XML, etc. and write a datastep import procedure (as proc import is a guessing procedure), otherwise you don't really have that much control over the data coming in.

[tanya_henderson]

This link is no longer functioning. http://support.sas.com/kb/35/563.html

[Kurt_Bremser]

---

@tanya_henderson wrote:

This link is no longer functioning. http://support.sas.com/kb/35/563.html

---

It is also not important. The message to take from this thread (and about a thousand others here on the communities) is to not use Excel files for data transfer.

[SAShole]

@Kurt_Bremser what would the best practice be if we are given an xlsx file as an input? convert to CSV?

[Kurt_Bremser]

For a one-off import, use PROC IMPORT with DBMS=XLSX or LIBNAME XLSX, but be aware of the limitations (incorrect guesses about variable types and attributes). You may (and sooner or later will) have to fix these to be able to do your analysis.

For something that needs to fit into a larger environment (joins with existing data), going the CSV (or other text file) path is the method of choice; Excel files cannot be used for consistent results.

[tanya_henderson]

Please note that this will only work on 9.4 or higher. I am still on 9.2 for now.

[Kurt_Bremser]

---

@tanya_henderson wrote:
Please note that this will only work on 9.4 or higher. I am still on 9.2 for now.

---

One of the gazillion reasons to upgrade to 9.4 a year before yesterday. But anyway, the most resilient method is always to save your data from Excel to a text (CSV, tab-separated, whatever floats your boat) file and read that with a data step. Using PROC IMPORT to bring data into SAS looks fine at first glance, but will cost additional work later to fix the inconsistencies caused by the guessing.

[Kurt_Bremser]

See maxims 22, 27 and 31.

Take control of the data transfer process and drop the unwieldy Excel file format.

[jos_monen]

To do this you need to save the Excel sheet as CSV, and read that using the guessingrows option. To do that  within SAS I made a macro using VBScript.  It exports the sheet as CSV in the Windows 'Temp' directory, reads it into SAS, and deletes the temporary files (script and csv file) .

 read/write the CSV file with semicolon delimiter, which is the default  format in my country. If your national settings use comma,  you have to change the delimiter in PROC Import into ','.

* Syntax: %ReadXLasCSV (<Excel path + filename>, <Excet worksheet name>,< Name SAS dataset>);

%macro ReadXLasCSV(XLFile, Sheet, Dataset);
option noxwait xsync;
data _null_;
temp = sysget('temp'); * Put the environment variable %temp% in SAS \ variable temp
call symput ('temp', right(trim(temp))); * put the content of temp in the SAS macrovariable &temp;
run;

data _null_;
File "&temp.\VBscript.vbs";
put 'Option Explicit';
put 'Dim oExcel';
put 'Set oExcel = WScript.CreateObject("Excel.Application")';
put 'Dim oBook';
s1 = 'Set oBook = oExcel.Workbooks.Open("' || "&XLname" ||'")';
put S1;
S2= 'oBook.worksheets("' || "&sheet" || '").activate';
put S2;
S3 = 'oBook.SaveAs "'||"&temp.\CSV.CSV"||'",6,,,,,,,,,,True';
put s3;
put 'oBook.Close False';
put 'oExcel.Quit';
put 'Set oExcel = Nothing';
run;

data _null_;
x "&temp.\VBscript.vbs";
run;

PROC IMPORT OUT= &dataset
DATAFILE= "&temp./csv.csv"
DBMS=CSV REPLACE;
DELIMITER=';';
GETNAMES=YES;
DATAROW=2;
guessingrows=MAX;
RUN;

data _null_;
fname="tempfile";
rc=filename(fname, "&temp./csv.csv");
if rc = 0 and fexist(fname) then
rc=fdelete(fname);
rc=filename(fname);
fname="tempfile";
rc=filename(fname, "&temp.\VBscript.vbs");
if rc = 0 and fexist(fname) then
rc=fdelete(fname);
rc=filename(fname);
run;

%mend;

[jos_monen]

Small mistake in macro definition: Should be
%macro ReadXLasCSV(XLName, Sheet, Dataset);

[Andrea_Peng]

Thanks for sharing the codes! I will have a try!

[Amanda_Lemon]

@jos_monen I tried running your code but am getting these warnings, followed by "ERROR: Physical file does not exist, C:\Desktop\Data\&temp.\VBscript.vbs." Do you know what might be causing this? Thanks in advance!

[RW9]

The answer to your question lies in the post you have made:

Warning: Apparent symbolic reference TEMP not resolved. 

This means the macro variable TEMP is not being created. 

Thus something is amis with the code (perhaps you are running on another OS or in a system where the environment variable does not exist):

data _null_;
temp = sysget('temp'); * Put the environment variable %temp% in SAS \ variable temp
call symput ('temp', right(trim(temp))); * put the content of temp in the SAS macrovariable &temp;
run;

However, more importantly, this thread is 3 years old.  Start a new thread with your question, inputs, outputs, test data etc.

I would advise against the use of VBS script as it may not work with your system. Are you trying to read in an Excel file?  No information to work with, hence why posting a full question would be betteras we have no idea what you are doing or how.