Hi,
I have a BP data where some of the id have missing data as '-9'. I want to change those missing values as 'NA'.
My data look like this
data have ;
id date SBP DBP
1 1/1/2011 122 84
1 3/1/2011 120 82
1 4/1/2011 -9 86
2 1/1/2011 110 76
2 2/1/2011 146 88
2 3/1/2011 140 -9
3 1/1/2011 124 80
3 2/1/2011 -9 88
3 3/1/2011 130 90
I want data like this
data want
id date SBP DBP
1 1/1/2011 122 84
1 3/1/2011 120 82
1 4/1/2011 NA 86
2 1/1/2011 110 76
2 2/1/2011 146 88
2 3/1/2011 140 NA
3 1/1/2011 124 80
3 2/1/2011 NA 88
3 3/1/2011 130 90
;
Kindly suggest
Thanks in advance.
If you want them to be as numeric then use format to identify invalid ones.
data test;
input val;
datalines;
12
-9
13
323
-9
;
run;
proc format ;
value valNA -9='NA'
other=[3.];
run;
data test;
set test;
format val valNA.;
run;
or you can create a new char variable using the format defined as :
data test;
set test;
new_val=put(val,valNA.);
run;
Do you want SBP and DBP to be numeric variables still?
No, not necessarily
If you want them to be as numeric then use format to identify invalid ones.
data test;
input val;
datalines;
12
-9
13
323
-9
;
run;
proc format ;
value valNA -9='NA'
other=[3.];
run;
data test;
set test;
format val valNA.;
run;
or you can create a new char variable using the format defined as :
data test;
set test;
new_val=put(val,valNA.);
run;
One way, preserving numeric values so sorts and calculations would work:
proc format library=work; invalue negnine -9 = .N other =[best8.] ; value na (default=8) .N = 'NA' ; run; data want; input id date :mmddyy10. SBP :negnine. DBP: negnine.; format date mmddyy10. SBP DBP na. ; datalines; 1 1/1/2011 122 84 1 3/1/2011 120 82 1 4/1/2011 -9 86 2 1/1/2011 110 76 2 2/1/2011 146 88 2 3/1/2011 140 -9 3 1/1/2011 124 80 3 2/1/2011 -9 88 3 3/1/2011 130 90 ; run;
I use a special missing .N instead of a simple missing so that later you could differentiate between coded as missing and other causes of missing.
You can do something like this
data have;
input id $ date:ddmmyy10. SBP DBP;
format date ddmmyy10.;
datalines;
1 1/1/2011 122 84
1 3/1/2011 120 82
1 4/1/2011 -9 86
2 1/1/2011 110 76
2 2/1/2011 146 88
2 3/1/2011 140 -9
3 1/1/2011 124 80
3 2/1/2011 -9 88
3 3/1/2011 130 90
;
proc format; value NA .='NA'; run;
data want;
set have;
if SBP=-9 then SBP=.;
if DBP=-9 then DBP=.;
format SBP DBP NA10.;
run;
proc print data=want;run;
You likely want it to be missing, not NA. I would recommend the special missing value, .N which gets you close.
See the fully worked example below, you can run it and examine the data sets. Otherwise, I would recommend a format since this is primarily for presentation, where you can show the missing values as NA. If you wanted, you could assign -9 as NA directly if you know the range of your data.
data class;
set sashelp.class;
if age in (12, 14) then do;
weight=.;
height = .N;
end;
run;
proc print data=class;
run;
proc format;
value NA_fmt
. = 'NA'
low - high = [8.1]
;
run;
title 'Height with Format and Weight with Special Missing .N';
proc print data=class;
var weight height;
format height NA_fmt.;
run;
@mehul4frnds wrote:
Hi,
I have a BP data where some of the id have missing data as '-9'. I want to change those missing values as 'NA'.
My data look like this
data have ;
id date SBP DBP
1 1/1/2011 122 84
1 3/1/2011 120 82
1 4/1/2011 -9 86
2 1/1/2011 110 76
2 2/1/2011 146 88
2 3/1/2011 140 -9
3 1/1/2011 124 80
3 2/1/2011 -9 88
3 3/1/2011 130 90
I want data like this
data want
id date SBP DBP
1 1/1/2011 122 84
1 3/1/2011 120 82
1 4/1/2011 NA 86
2 1/1/2011 110 76
2 2/1/2011 146 88
2 3/1/2011 140 NA
3 1/1/2011 124 80
3 2/1/2011 NA 88
3 3/1/2011 130 90
;
Kindly suggest
Thanks in advance.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.