Converting <.0001 values into a usable format.

[harryg]

I have several fields that have <.0001 values that I would like to display and use as numbers.  Is there a generalized method for converting these to numeric values so I can export the table to Excel without having to replace the "less than" sign?

stat x y z

pVALUE _ 0.0129 0.2078.....

pVALUE 0.0129 _ <.0001

pVALUE 0.2078 <.0001 _

pVALUE 0.9440 0.2271 0.0002

pVALUE 0.0068 <.0001 <.0001

pVALUE <.0001 0.0342 0.8768

pVALUE 0.0159 0.0032 0.0180

pVALUE 0.2688 0.8567 0.3218

pVALUE 0.6643 0.0029 0.0804

[Hima]

How would you like to display <.0001? Meaning you want to view it as 0.0001 or something else?

[harryg]

Ideally, I would like to just drop the "<" sign for all the values in the matrix and display the normal rounded values.  I'd like to do this for all the fields in the dataset (which is a dynamic matrix) without processing the columns independently which is currenly where I'm at.  I appreciate your time..

pVALUE _

pVALUE .01289

pVALUE .20785

pVALUE .94402

pVALUE .00682

pVALUE .00001

pVALUE .01586

pVALUE .26879

pVALUE .66426

[Hima]

I am attaching sample code for you. Please adjust to your requirement.

RSUBMIT;
DATA TEMP;
INPUT stat $ x $ y $ z$ ;
DATALINES;
pVALUE 0.0129 0.2078 .
pVALUE 0.0129 _ <.0001
pVALUE 0.2078 <.0001 _
pVALUE 0.9440 0.2271 0.0002
pVALUE 0.0068 <.0001 <.0001
pVALUE <.0001 0.0342 0.8768
pVALUE 0.0159 0.0032 0.0180
pVALUE 0.2688 0.8567 0.3218
pVALUE 0.6643 0.0029 0.0804
;
RUN;

proc format;
value $x
"<.0001"  = "0.0001";
run;

data new;
set temp;
format x x.;
format y x.;
format z x.;
run;

proc print; run;

Output:

Obs     stat       x       y         z

1     pVALUE    0.0129    0.2078
2     pVALUE    0.0129    _         0.0001
3     pVALUE    0.2078    0.0001    _
4     pVALUE    0.9440    0.2271    0.0002
5     pVALUE    0.0068    0.0001    0.0001
6     pVALUE    0.0001    0.0342    0.8768
7     pVALUE    0.0159    0.0032    0.0180
8     pVALUE    0.2688    0.8567    0.3218
9     pVALUE    0.6643    0.0029    0.0804

[Astounding]

If these pvalues are generated by a SAS procedure, you can do better than "converting" the <.0001.  ODS will create an output data set holding the actual pvalue, rather than "<.0001".

You might have to remove the format that causes the small values to print as "<.0001".

[harryg]

Thanks for the reply.  I already output to an ODS table, but how do I drop the format on the pvalues?

ODS output SimpleStats=ModelName_ExpStatsA PearsonCorr=ModelName_ExpCorrA;

Proc Corr Data=ExposuresND;

With Depar; Var FieldList;

ODS Listing;

[art297]

Depends upon which ODS table you want to modify but, in all cases, the easiest place to make the change is to change the template.  Take a look at: http://support.sas.com/kb/23/352.html

[harryg · Accepted solution]

This seemed to work nicely and is a good generalized solution.   Is this a permanent change to my base.core.stackedmatrix template or does it need to be run each time?   I've not worked with templates before. Thanks!

[harryg]

Dropping/converting the formats on the ODS result dataset  seemed to work.  Thanks!

Data ModelName_ExpCorrA; Set ModelName_ExpCorrA; format pDepVar pFactorList 6.5;