DATA Step, Macro, Functions and more

empty cells in Excel

Reply
Occasional Contributor
Posts: 13

empty cells in Excel

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.

PROC Star
Posts: 7,487

Re: empty cells in Excel

Posted in reply to vicdicenzo

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

 

Occasional Contributor
Posts: 13

Re: empty cells in Excel

Hi Art,

 

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

 

 

/** Import an XLSX file. **/
78
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?
PROC Star
Posts: 7,487

Re: empty cells in Excel

Posted in reply to vicdicenzo

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

 

Super User
Posts: 7,841

Re: empty cells in Excel

Posted in reply to vicdicenzo

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

Re: empty cells in Excel

Posted in reply to KurtBremser

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. **/
78
79 PROC IMPORT DATAFILE="/folders/myfolders/2016/SALUDA/EFFORT.csv"
80 OUT=WORK.EFFORT
81 DBMS=CSV
82 REPLACE;
83
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.
Super User
Super User
Posts: 7,074

Re: empty cells in Excel

Posted in reply to vicdicenzo

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;

 

Ask a Question
Discussion stats
  • 6 replies
  • 406 views
  • 0 likes
  • 4 in conversation