01-10-2018 06:12 AM
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;
01-10-2018 06:27 AM
Do you mean like this?
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.
01-10-2018 07:09 AM
See maxims 22, 27 and 31.
Take control of the data transfer process and drop the unwieldy Excel file format.