05-06-2017 04:54 PM
I have an excel file with several empty cells. I am trying to use Proc Means on one of the variables with the empty cells but I keep getting an error that says "variable does not match the type prescribed for this list"...How do I deal with empty cells? I have tried leaving them blank and putting a . in the cell but neither has worked. Thanks.
05-06-2017 05:31 PM
I think the problem has to do with the otherwise numeric cells, in Excel, containing blanks. As such, SAS will convert the variable to be a character variable.
If you can use the Excel engine, you can force such variables to be numeric and automatically convert the blank cells to missing values. e.g.:
proc import datafile="/folders/myfolders/class.xlsx" out=want dbms=excel replace; DBDSOPTS= "DBTYPE=(age='NUM(8)'"; sheet='Sheet1'
Otherwise, you'd have to create a new, numeric variable by input(ting) the character variable. e.g.:
data want (drop=_:); set have (rename=(age=_age)); age=input(_age,?? 8.); run;
Art, CEO, AnalystFinder.com
05-08-2017 07:41 AM
Thanks for the tips. I tried the first suggestion and received an error:
05-07-2017 03:44 AM
The only real solution to this kind of problems is to use a sensible, reliable file format for the data transfer; this allows you to use a well-defined process (data step) where you have control over how the data is imported. Both proc import and libname excel have to guess what is where, and that is sub-optimal to say the least.
So save from Excel as csv, use proc import once for getting a data step, extract that from the log and adapt it to your needs.
05-08-2017 07:50 AM
OK thanks. What is the DBMS= code? I tried Proc Import of a csv file and it did not work. Thanks.
05-08-2017 10:12 AM
We have seen this error before on CSV files.
Unable to sample external file, no data in first 5 records. ERROR: Import unsuccessful. See SAS Log for details.
In the past it was caused by someone using Macintosh version of Excel to create the CSV file. By default that version of Excel will create a text file that uses just a CR character ('0D'x) as the end of line. There should be other output types in Macintosh version of Excel that will let you create a file with normal end of line characters. perhaps something like CSV for MS DOS .
If you can't recreate it try using a FILENAME statement to let SAS know that CR is the end of line character.
filename myfile "/folders/myfolders/2016/SALUDA/EFFORT.csv" termstr=cr; PROC IMPORT DATAFILE=myfile OUT=WORK.EFFORT REPLACE DBMS=CSV ; RUN;