BookmarkSubscribeRSS Feed
vicdicenzo
Obsidian | Level 7

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.

6 REPLIES 6
art297
Opal | Level 21

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

 

vicdicenzo
Obsidian | Level 7

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?
art297
Opal | Level 21

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

 

Kurt_Bremser
Super User

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.

vicdicenzo
Obsidian | Level 7

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.
Tom
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4487 views
  • 0 likes
  • 4 in conversation