topic Re: how to display missing value as character in SAS Programming

how to display missing value as character

magicdj — Wed, 27 Sep 2023 17:25:44 GMT

i set up some missing value as below. in my excel output, i want the non missing numbers still the numbers, but for missing values , i want it show as NA. is there any way to do it? thanks

data Have;
Set sashelp.class;
if age=11 then age=.;

run;

proc export data=Have
outfile="/usr/local/SAS/SASUsers/LabRet.excel"
dbms=tab
label
replace;
run;

Re: how to display missing value as character

Tom — Wed, 27 Sep 2023 17:35:46 GMT

Why? What extra information does that add?

You could define a format that does that.

proc format;
  value na . = 'NA' other=[best12.];
run;

data test;
  set sashelp.class(obs=1);
  do age=age,.,._,.A,.Z ;
    output;
  end;
run;

proc print data=test;
  format age na.;
run;

Result

Obs     Name     Sex        Age         Height    Weight

 1     Alfred     M               14      69       112.5
 2     Alfred     M     NA                69       112.5
 3     Alfred     M                _      69       112.5
 4     Alfred     M                A      69       112.5
 5     Alfred     M                Z      69       112.5

If you want the other 27 missing values to also print as NA then define the format like this instead:

value na ._-.Z = 'NA' other=[best12.];

value na low-high=[best12.] other='NA';

Re: how to display missing value as character

magicdj — Wed, 27 Sep 2023 18:06:45 GMT

hi, Tom the sas dataset show NA, ut when i export to excel. the result is not show. don't know what happened.

Re: how to display missing value as character

magicdj — Wed, 27 Sep 2023 19:04:38 GMT

found the answer

Solved: How to retain the Variable Format when PROC EXPORT TO EXCEL - SAS Support Communities