topic Re: import '0' instead of '.' from excel in SAS Programming

import '0' instead of '.' from excel

Ivan555 — Wed, 11 Dec 2019 18:47:09 GMT

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!

Re: import '0' instead of '.' from excel

PaigeMiller — Wed, 11 Dec 2019 18:46:21 GMT

options missing=0;

Re: import '0' instead of '.' from excel

Reeza — Wed, 11 Dec 2019 18:58:49 GMT

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?

Re: import '0' instead of '.' from excel

PaigeMiller — Wed, 11 Dec 2019 20:24:59 GMT

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.

Re: import '0' instead of '.' from excel

Reeza — Wed, 11 Dec 2019 20:29:19 GMT

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.

Re: import '0' instead of '.' from excel

PaigeMiller — Wed, 11 Dec 2019 20:33:22 GMT

@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)

Re: import '0' instead of '.' from excel

Reeza — Wed, 11 Dec 2019 20:59:55 GMT

Excel also gave you negative standard deviations at one point 🙂