topic Re: For all the Numeric variable if empty replace with Null in New SAS User

For all the Numeric variable if empty replace with Null

animesh123 — Fri, 21 Feb 2025 20:47:27 GMT

is there way to find all the Numeric variable and if obs is empty or . (dot), i want to replace all with Null value in one go

Getting wrong output if blank value for numeric field

animesh123 — Fri, 21 Feb 2025 20:36:32 GMT

I have dataset where the column is integer

while writing this into flat file or in sasdataset i am getting .(dot) instead of populating null value,

same dataset when exporting into excel using sas eg in there its coming blank

is there way to fix this to populate only Null for integer value rather than .(dot) also the same in text file where am writing into flat/text file

Re: For all the Numeric variable if empty replace with Null

Cynthia_sas — Fri, 21 Feb 2025 20:58:54 GMT

Hi:
If you want to display a blank instead of a . for a missing numeric variable value, this is possible by using the MISSING= system option.
However, SAS internally stores missing values for numeric variables as a period/dot -- this is the internal representation of a missing value. SAS does not have the same concept of null values that you find in some databases or in a language like COBOL. I suggest you clarify what you mean by null values and where you want to change the values -- in an output report or in stored SAS data values.
Cynthia

Re: For all the Numeric variable if empty replace with Null

animesh123 — Fri, 21 Feb 2025 21:09:11 GMT

I am trying to write the output into Flat file but when transferring into flat file am getting .(dot) am trying to replace with null value

Re: For all the Numeric variable if empty replace with Null

animesh123 — Fri, 21 Feb 2025 21:13:45 GMT

NUll value means blank value

Re: For all the Numeric variable if empty replace with Null

SASKiwi — Fri, 21 Feb 2025 21:20:09 GMT

Add this OPTIONS statement to the start of your program to write out blanks instead of dots in your flat file:

options missing = ' ';

Re: Getting wrong output if blank value for numeric field

Tom — Fri, 21 Feb 2025 23:09:49 GMT

I do not understand what you are saying. Actual data examples would help much more than photographs to explain what you are asking about.

In SAS a MISSING numeric value (which what I assume you mean by NULL) is represented in CODE by a period. SAS also allows 27 other (special) missing values that are represented in CODE as a period followed by a single letter or the underscore character.

SAS will treat character variables that only contain spaces a missing.

When writing a numeric value to a text file using the default numeric format a normal missing value is represented by the character set with the MISSING= option. The default character used by the missing option is a period. Special missing values will be printed as letter used to represent them in the code.

When reading either a numeric or a character variable from a text file into SAS variables using the normal numeric or character informat a single period will be converted to a MISSING value. To allow single letters to be read by the normal numeric informat as special missing values list those letters (or underscore) in the MISSING statement.

See the documentation:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p0ueqauvtyb2hqn1mj79t26azlxv.htm

Note also that when writing a text file use the DSD option on the FILE statement then missing values will take NO space in the text file. The delimiters will be placed right next to each other. To treat adjacent delimiters as indicating a missing value when reading a delimited text file make sure to include the DSD option on the INFILE statement.

Re: Getting wrong output if blank value for numeric field

ballardw — Sat, 22 Feb 2025 09:19:27 GMT

Are you asking to see text that actually says NULL?

That would require custom formats to associate missing values, by default displayed with the dot, and might be a little bit tricky depending on individual variable attributes such as existing formats.

Re: Getting wrong output if blank value for numeric field

Kurt_Bremser — Sat, 22 Feb 2025 10:45:47 GMT

I combined your posts dealing with the same issue.

Re: For all the Numeric variable if empty replace with Null

Tom — Sat, 22 Feb 2025 18:51:37 GMT

@animesh123 wrote:
I am trying to write the output into Flat file but when transferring into flat file am getting .(dot) am trying to replace with null value

Show the code you used to write the file. Explain the layout you want in the file.

Note if you are writing a delimited file then the PUT statement should already be writing "null" (that is nothing at all) for missing values. If it is writing whatever character you set with the MISSING option instead then you probably forgot to include the DSD option in the FILE statement.