DATA Step, Macro, Functions and more

I want to replace missing values with 0

Reply
Contributor VRD
Contributor
Posts: 33

I want to replace missing values with 0

textordXXX1xxxx2xxxx3cccc4bbbb2nnnn2mmm2nnbhgb2
All Su10149625153976429
Exp1576018533050436
All [identified risk]209 ( 0.6)     9  1.266 ( 2.6)    78  4.2132 ( 3.3)   190  6.219 ( 4.4)    19  4.43 (  . )     3 .13 (  . )    13 .
Meand fsdf gdfag304 ( 0.3)     4  0.545 ( 1.8)    51  2.890 ( 2.3)   138  4.511 ( 2.6)    11  2.53 (  . )     3 .7 (  . )     7 .
bdgngndsjmfhjmfh304 ( 0.3)     4  0.545 ( 1.8)    51  2.890 ( 2.3)   138  4.511 ( 2.6)    11  2.53 (  . )     3 .7 (  . )     7 .
dgnsfgmn304 ( 0.3)     4  0.544 ( 1.7)    50  2.788 ( 2.2)   136  4.511 ( 2.6)    11  2.52 (  . )     2 .7 (  . )     7 .
dxv rthfrthsfbn301 ( 0.0)     1  0.12 ( 0.1)     2  0.11 (  . )     1 .
bhdsfgnfsdgmnfhs305 ( 0.3)     5  0.725 ( 1.0)    27  1.550 ( 1.3)    52  1.78 ( 1.9)     8  1.86 (  . )     6 .
dbdbdsgjnfgjmnfsgjmnf305 ( 0.3)     5  0.725 ( 1.0)    27  1.548 ( 1.2)    50  1.66 ( 1.4)     6  1.46 (  . )     6 .
fghnsfggbn cvbnsfgja305 ( 0.3)     5  0.725 ( 1.0)    27  1.544 ( 1.1)    46  1.53 ( 0.7)     3  0.76 (  . )     6 .
sjhngfjhnsdfhjasdthn304 ( 0.1)     4  0.13 ( 0.7)     3  0.7
tasjntgshnsgsgsdfng302 ( 0.1)     2  0.12 ( 0.5)     2  0.5
sngdggfnasdfgnasdna302 ( 0.1)     2  0.12 ( 0.5)     2  0.5
Super User
Super User
Posts: 7,407

Re: I want to replace missing values with 0

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;

Contributor VRD
Contributor
Posts: 33

Re: I want to replace missing values with 0

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?

Super User
Super User
Posts: 7,407

Re: I want to replace missing values with 0

Try

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

Repeat for each variable in your table.

Contributor VRD
Contributor
Posts: 33

Re: I want to replace missing values with 0

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 )");

Super User
Super User
Posts: 7,407

Re: I want to replace missing values with 0

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.

Valued Guide
Posts: 3,208

Re: I want to replace missing values with 0

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
 

---->-- ja karman --<-----
Super User
Posts: 10,516

Re: I want to replace missing values with 0

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='.';

Moderator
Posts: 238

Re: I want to replace missing values with 0

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?

    Ask a Question
    Discussion stats
    • 8 replies
    • 422 views
    • 1 like
    • 5 in conversation