DATA Step, Macro, Functions and more

How to import excel with guessingrow function

Reply
Contributor
Posts: 21

How to import excel with guessingrow function

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;

 

 

Super User
Super User
Posts: 9,200

Re: How to import excel with guessingrow function

Posted in reply to Andrea_Peng

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.

 

 

Super User
Posts: 9,560

Re: How to import excel with guessingrow function

Posted in reply to Andrea_Peng

See maxims 22, 27 and 31.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 2 replies
  • 122 views
  • 0 likes
  • 3 in conversation