topic Re: Handling Missing values in file writer transformation in SAS Programming

Handling Missing values in file writer transformation

neha11_ch — Thu, 01 Apr 2021 15:44:03 GMT

While using file writer transformation to export data in csv file with semicolon as delimiter, missing values are exported as dot.

but instead there should be two consecutive delimiters. Any suggestion to get the expected result?

Re: Handling Missing values in file writer transformation

PhilC — Thu, 01 Apr 2021 20:33:10 GMT

according to this: Solved: PROC EXPORT produces "0" for blank or null values - SAS Support Communities

"file writer transformation" is a "PROC EXPORT" and OPTIONS MISSING sets the character.

I didn't test this but if you can set this before you run the "file writer transformation":

options missing=%sysfunc(byte(255));

I got this from Joe:

how to set missing values to NULL in SAS - Stack Overflow

Re: Handling Missing values in file writer transformation

Patrick — Mon, 05 Apr 2021 14:03:09 GMT

A dot is the default character printed for missing numerical values. I don't have access to DIS in the moment so can't check if the file writer transformation provides any setting to change this.

If there isn't a setting/option to change the value for missing then what @PhilC suggests should work.

If you want to implement clean then have in the pre-code of the transformation:

%let sv_missing=%sysfunc(getoption(missing,KEYWORD));
options missing=' ';

And in the post-code:

options &sv_missing;

Re: Handling Missing values in file writer transformation

neha11_ch — Mon, 05 Apr 2021 12:23:52 GMT

Thanks for the revert. Below piece of code is displaying space instead of . for missing numeric value.

options missing=%sysfunc(byte(255));

The expected result is two consecutive delimiters. No space in between. Can you may be help me on this?

Re: Handling Missing values in file writer transformation

Tom — Mon, 05 Apr 2021 12:37:29 GMT

I assume this is not really a programming question (you didn't post any code that you ran and "file writer" is not a term used by programmers.)

Are you using some tool to generate this file? If so what tool? How did you trigger it? What options does the tool provide? Which did you use? Does it produce a SAS log that you can share so we can see what code it ran?

Does the tool have an EXPORT feature instead of a FILE WRITER feature? If so then that would probably be more appropriate for generating a CSV file, so it might have the functionality to generate a proper CSV file.

Re: Handling Missing values in file writer transformation

PhilC — Mon, 05 Apr 2021 13:46:22 GMT

OK, I'm sorry, I am out of my depth. Your system's encoding may come in to play on this. I naively suspect that the solution I linked to on Stack Overflow may have been using wlatin encoding but we have not established what encoding your system or DI uses, if it is different.

did you try the other suggestion?

options missing="%sysfunc(byte(0))";