- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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'
run;
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Art,
Thanks for the tips. I tried the first suggestion and received an error:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The first suggestion was to use the EXCEL (not XLSX) engine .. if you can. It does allow one to use the DBDSOPTS option.
Art, CEO, AnalystFinder.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OK thanks. What is the DBMS= code? I tried Proc Import of a csv file and it did not work. Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;