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;
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.];
Or
value na low-high=[best12.] other='NA';
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.];
Or
value na low-high=[best12.] other='NA';
hi, Tom the sas dataset show NA, ut when i export to excel. the result is not show. don't know what happened.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.