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;

 

 

13 REPLIES 13
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!

Amanda_Lemon
Quartz | Level 8

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

 

Amanda_Lemon_0-1720019720224.png

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 12933 views
  • 5 likes
  • 7 in conversation