Regarding replacing missing values '-9' to 'NA'

[mehul4frnds]

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.

[PeterClemmensen]

Do you want SBP  and  DBP to be numeric variables still?

[mehul4frnds]

No, not necessarily 

[SuryaKiran · Accepted solution]

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;

[ballardw]

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.

[PeterClemmensen]

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;

[Reeza]

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.

---