topic Re: I want to replace missing values with 0 in SAS Programming

I want to replace missing values with 0

VRD — Wed, 03 Sep 2014 09:29:19 GMT

text	ord	XXX1	xxxx2	xxxx3	cccc4	bbbb2	nnnn2	mmm2n	nbhgb2
All Su	10	1496	2515	3976	429
Exp	15	760	1853	3050	436
All [identified risk]	20	9 ( 0.6) 9 1.2	66 ( 2.6) 78 4.2	132 ( 3.3) 190 6.2	19 ( 4.4) 19 4.4	3 ( . ) 3 .	13 ( . ) 13 .
Meand fsdf gdfag	30	4 ( 0.3) 4 0.5	45 ( 1.8) 51 2.8	90 ( 2.3) 138 4.5	11 ( 2.6) 11 2.5	3 ( . ) 3 .	7 ( . ) 7 .
bdgngndsjmfhjmfh	30	4 ( 0.3) 4 0.5	45 ( 1.8) 51 2.8	90 ( 2.3) 138 4.5	11 ( 2.6) 11 2.5	3 ( . ) 3 .	7 ( . ) 7 .
dgnsfgmn	30	4 ( 0.3) 4 0.5	44 ( 1.7) 50 2.7	88 ( 2.2) 136 4.5	11 ( 2.6) 11 2.5	2 ( . ) 2 .	7 ( . ) 7 .
dxv rthfrthsfbn	30	1 ( 0.0) 1 0.1	2 ( 0.1) 2 0.1	1 ( . ) 1 .
bhdsfgnfsdgmnfhs	30	5 ( 0.3) 5 0.7	25 ( 1.0) 27 1.5	50 ( 1.3) 52 1.7	8 ( 1.9) 8 1.8	6 ( . ) 6 .
dbdbdsgjnfgjmnfsgjmnf	30	5 ( 0.3) 5 0.7	25 ( 1.0) 27 1.5	48 ( 1.2) 50 1.6	6 ( 1.4) 6 1.4	6 ( . ) 6 .
fghnsfggbn cvbnsfgja	30	5 ( 0.3) 5 0.7	25 ( 1.0) 27 1.5	44 ( 1.1) 46 1.5	3 ( 0.7) 3 0.7	6 ( . ) 6 .
sjhngfjhnsdfhjasdthn	30	4 ( 0.1) 4 0.1	3 ( 0.7) 3 0.7
tasjntgshnsgsgsdfng	30	2 ( 0.1) 2 0.1	2 ( 0.5) 2 0.5
sngdggfnasdfgnasdna	30	2 ( 0.1) 2 0.1	2 ( 0.5) 2 0.5

Re: I want to replace missing values with 0

RW9 — Wed, 03 Sep 2014 09:46:28 GMT

Will need some more information. Where does the output come from, where are the missings being generated, presuming the variables are text etc. I would assume that you have some stats procedure which generates the numbers, and then a datastep to create the text columns from that, so just add in;

data want;

set computations;

attrib output_text format=$20.;

text=strip(put(mean,5.2))||" (";

if std=. then text=strip(text)||"0)";

else text=strip(text)||strip(put(std,5.2));

run;

Re: I want to replace missing values with 0

VRD — Wed, 03 Sep 2014 10:27:48 GMT

It is just a final data set which i need to use in the report.

These are counts of N (%) E R.

Can i use something as if ord=30 and replace the missing values of all treaments into a 0 (0.0) 0 0?

Re: I want to replace missing values with 0

RW9 — Wed, 03 Sep 2014 10:51:57 GMT

Try

tranwrd(variable,"( . )","( 0 )");

Repeat for each variable in your table.

Re: I want to replace missing values with 0

VRD — Wed, 03 Sep 2014 11:05:44 GMT

I tried it but unfortunately it didnt work.

Also the values in the variable are in character so i followed your step like this.

rtp=tranwrd(variable,"( )","( 0 )");

Re: I want to replace missing values with 0

jakarman — Wed, 03 Sep 2014 11:21:04 GMT

Get back to the fundamentals of missing in SAS SAS(R) 9.4 Language Reference: Concepts, Third Edition
When the question is about the . reading the file. Just use that and interpreted them as missing in SAS.
When the question is about printing/reporting data and the value is missing. Review the SAS format usage.
When it is about working with missings in code. Code that in SAS conform the SAS missing principles

Re: I want to replace missing values with 0

RW9 — Wed, 03 Sep 2014 12:08:49 GMT

Sorry, no longer following you. You posted output in your first post which showed ( .), and you said you wanted to replace the . with 0's. In my initial response I indicated, as per Jaap Karman, that missing should be handled in the procedure that created the data. However you then said you only had the final dataset, which as there is non-numeric data within has to be character. Therefore using tranwrd to replace the text part with a zero should be used. Hence I assumed that the text: tranwrd(variable,"( . )","( 0 )");

Should work, the ( .) I copied from your example, you might want to check that with the data, and the ( 0) is what I assumed you wanted. You will need to apply the tranwrd to every column you want replaced,

tranwrd(xxxx1,"( . )","( 0 )");

tranwrd(xxxx2,"( . )","( 0 )");

And ensure the find string is per your data and the replace string is as you want it.

Re: I want to replace missing values with 0

ballardw — Wed, 03 Sep 2014 14:44:24 GMT

If this is the result of a proc then before the procedure that generates the report:

Options missing='0';

Then all missing values will be displayed as a zero in the output.

A good idea to reset afterwards:

options missing='.';

Re: I want to replace missing values with 0

AndrewHowell — Thu, 04 Sep 2014 07:14:58 GMT

Just to clarify, do you wish to:

Replace the stored value of missing with a stored value of zero?
Keep the stored value as missing, but display it with a "0" rather than a "."?

A stored value of 0 (displayed as a 0) and a missing value displayed as a 0 are not the same thing.

To display missing stored values as 0, see post #7.

To replace text of ( . ) with text of ( 0 ), see post #6.

data new(drop=i);

set old;

/* The following replaces stored numeric missings with stored values of 0 */

array MyNums

_NUMERIC_ ;

do i = 1 to dim(MyNums);

MyNums=coalesce( MyNums , 0 );

end;

/* The following replaces all occurrences of one character string with another */

array MyChars

_CHARACTER_ ;

do i = 1 to dim(MyChars);

MyChars=tranwrd( MyChars, "( . )" /* Old text */ ,"( 0 )" /* New Text */ );

end;

run;

BTW, I cannot tell if your displays of missing contain one or two blanks either side of the period. It appears to be 2 on the left hand side, 1 on the right hand side?