import '0' instead of '.' from excel

[Ivan555]

Hello!

This may be a common task, but I cannot find a better way to solve it.

When importing to SAS from Excel, in the numeric fields I get the '.' values  if in Excel cells I have blanks.

Could you please tell in case of fields having blanks if it is possible to get  '0'  values instead of '.' during import? 

Thanks!

[PaigeMiller]

options missing=0;

[Reeza]

Yeah, this isn't a common task and replacing missing with 0 can be dangerous and not valid in many circumstances so it's handled by a one off solution.

Is it many variables? PaigeMillers solution will change how it appears but not necessarily how it's handled. If you were doing an average calculation, would you want 0's included in the average and count or not?

[PaigeMiller]

I can't think of a situation where you would want missings to be considered a zero for data analysis purposes. I think it makes sense to handle them as missing, even if it shows as a zero, which is what OPTIONS MISSING=0; does.

The drawback to OPTIONS MISSING=0; is that every missing in the entire data set (and all other data sets) will appear as zero until you turn the option back to OPTIONS MISSING=.; There is no way to limit this to just a single data set or to certain columns in a data set.

[Reeza]

Neither can I, but I suspect the OP doesn't know the difference between a number appearing as 0 and still being missing as the underlying value.

[PaigeMiller]

---

@Reeza wrote:
Neither can I, but I suspect the OP doesn't know the difference between a number appearing as 0 and still being missing as the underlying value.

---

I seem to recall that if you go back 20 years, Excel didn't know the difference between 0 and missing; and all missings were treated as zeros , giving you incorrect averages and so on. (But that has been fixed)

[Reeza]

Excel also gave you negative standard deviations at one point 🙂