BookmarkSubscribeRSS Feed
Andrea_Peng
Obsidian | Level 7

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;

 

 

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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

Kurt_Bremser
Super User

@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
Pyrite | Level 9

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

Kurt_Bremser
Super User

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
Obsidian | Level 7
Please note that this will only work on 9.4 or higher. I am still on 9.2 for now.


Kurt_Bremser
Super User

@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.

jos_monen
Calcite | Level 5

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
Calcite | Level 5
Small mistake in macro definition: Should be
%macro ReadXLasCSV(XLName, Sheet, Dataset);
Andrea_Peng
Obsidian | Level 7

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

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
  • 11 replies
  • 10374 views
  • 2 likes
  • 6 in conversation