empty cells in excel

[vicdicenzo]

I am importing excel data using proc import. I have a numeric variable with some missing cells (blank) but get error message:

PROC MEANS NOPRINT; BY LAKE YEAR; VAR POUNDS;
 ERROR: Variable pounds in list does not match type prescribed for this list.

How do I compute descriptive stats with blank cells?

[ballardw]

Your problem is only loosely related to blank cells. The exact error you are showing is that the variable on the VAR statement is character. You cannot take the "mean" of a character variable.

If you used Proc import to bring the data into SAS from a spreadsheet then only the first 20 or so rows of data are examined. If all of those rows have a blank value then the variable will be assumed to be character. You may also have an incorrect length such that all of the actual character values imported that look like numbers may be truncated. Did you look at the values of the variable POUNDS after bringing into SAS to see what the values might look like?

You can use Proc Freq to get the actual values and counts of values for the variable.

If those look appropriate you can create a numeric variable with code such as:

data want;

   set have;

   numpounds = input(pounds, f5.);

run;

if the number of digits plus decimal point if any exceeds 5 then replace the 5 with the longest number of digits plus the decimal character.

If by any chance your Pounds is currency then use input(pounds, comma10.) and adjust the number to match expected number of digits plus currency symbol, commas and decimal point.

Importing directly from Excel has many pitfalls because of the things people do to the files and the fact that columns are not required to be consistent for data types unlike the variables in SAS.

You might be better off by 1) saving the Excel file to a CSV format, 2) use import to read the CSV file and set the option for the guessingrows to be MAX if using code or a number close to the number of rows in the file if using an import wizard.

[vicdicenzo]

Thanks, but pounds is not a character variable - it is numeric (not currency).  There are just a number of missing cells.  I cannot use prof freq because I am trying to compute a mean.

[ballardw]

---

@vicdicenzo wrote:

Thanks, but pounds is not a character variable - it is numeric (not currency).  There are just a number of missing cells.  I cannot use prof freq because I am trying to compute a mean.

---

I did not say to use Proc Freq to calculate your wanted result but to examine the values actually occurring in your data set.

It is quite possible that if a column that should be numeric but gets imported as character is shorter than needed. You might expect that you have a value of 135.46 in your data but the IMPORTED character variable only holds 13 (or even 1). Proc Freq will show the characters values read so that you can verify that the expected values were read. Then if so, a correct numeric variable can be created using the INPUT function so you can calculate your needed mean.

Did you try the example data step to convert the character values to numeric?

If not, why not?

[LaurieF]

If you know for sure that at least one row will have a populated pounds value, put the following as a statement in your proc import:

guessingrows=n;

where n is the number of rows you want to scan before you do the import. Once the import process has detected that, "Oh yes, I see now. This pound column is numeric! Silly me!", it'll change all the blank cells to numeric missing. And your subsequent code should work. It saves programmatic work-arounds.

[andreas_lds]

Afaik guessingrows is not available when importing excel files @LaurieF.