topic Re: empty cells in Excel in SAS Programming

empty cells in Excel

vicdicenzo — Sat, 06 May 2017 20:54:13 GMT

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.

Re: empty cells in Excel

art297 — Sat, 06 May 2017 21:31:02 GMT

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

Re: empty cells in Excel

Kurt_Bremser — Sun, 07 May 2017 07:44:34 GMT

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.

Re: empty cells in Excel

vicdicenzo — Mon, 08 May 2017 11:41:17 GMT

Hi Art,

Thanks for the tips. I tried the first suggestion and received an error:

/** Import an XLSX file. **/

79 PROC IMPORT DATAFILE="/folders/myfolders/2016/SALUDA/EFFORT.XLSX"

80 OUT=WORK.EFFORT

81 DBMS=XLSX

82 REPLACE;

NOTE: The previous statement has been deleted.

83 DBDSOPTS= "DBTYPE=(age='NUM(8)'";

________

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

84 sheet='Sheet1';

85 RUN;

Seems like the DBDSOPTS is incorrect?

Re: empty cells in Excel

vicdicenzo — Mon, 08 May 2017 11:50:01 GMT

OK thanks. What is the DBMS= code? I tried Proc Import of a csv file and it did not work. Thanks.

/** Import an XLSX file. **/

79 PROC IMPORT DATAFILE="/folders/myfolders/2016/SALUDA/EFFORT.csv"

80 OUT=WORK.EFFORT

81 DBMS=CSV

82 REPLACE;

84 RUN;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary

parameter values will be saved to WORK.PARMS.PARMS.SLIST.

Unable to sample external file, no data in first 5 records.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

Re: empty cells in Excel

art297 — Mon, 08 May 2017 13:06:05 GMT

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

Re: empty cells in Excel

Tom — Mon, 08 May 2017 14:12:49 GMT

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;