topic Re: Regarding replacing missing values '-9' to 'NA' in SAS Programming

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

mehul4frnds — Thu, 11 Oct 2018 19:57:48 GMT

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.

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

PeterClemmensen — Thu, 11 Oct 2018 20:00:10 GMT

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

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

mehul4frnds — Thu, 11 Oct 2018 20:01:25 GMT

No, not necessarily

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

PeterClemmensen — Thu, 11 Oct 2018 20:09:45 GMT

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;

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

SuryaKiran — Thu, 11 Oct 2018 20:10:40 GMT

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;

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

Reeza — Thu, 11 Oct 2018 20:14:46 GMT

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.

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

ballardw — Thu, 11 Oct 2018 20:15:11 GMT

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.