topic Re: Replacing Excel Formula in SAS in SAS Programming

Replacing Excel Formula in SAS

asherer — Wed, 20 Feb 2019 20:51:02 GMT

I am trying to convert an excel formula into sas and currently struggling. The excel formula is

=IF(H11<0,IF(NORMSDIST(H11)<0.0001,"<.0001",NORMSDIST(H11)),IF(1-NORMSDIST(H11)<0.0001,"<.0001",1-NORMSDIST(H11)))

I am trying to convert this to SAS and H11 equals a field called TSTAT.. I believe I am to use PROBNORM but cant figure out the if part.

if(T1.TSTAT <0,if (PROBNORM(T1.TSTAT) <0.0001,"<.0001",PROBNORM(T1.TSTAT)),if (1-PROBNORM(T1.TSTAT)<0.0001,"<.0001",1-PROBNORM(T1.TSTAT)))

Re: Replacing Excel Formula in SAS

Reeza — Wed, 20 Feb 2019 20:58:08 GMT

Can you unchain those IFs to make it more legible? A series of IF/THEN statements would be significantly easier to manage. SAS doesn't have IF as you're attempting to use there. It does have IFN/IFC.

Re: Replacing Excel Formula in SAS

asherer — Wed, 20 Feb 2019 21:16:30 GMT

So My excel code reads

=IF(H11<0,
IF(NORMSDIST(H11)<0.0001,"<.0001",NORMSDIST(H11)),
IF(1-NORMSDIST(H11)<0.0001,"<.0001",1-NORMSDIST(H11)))

I would want to replace the IF to

IFC(T1.TSTAT<0,
IFC(PROBNORM(T1.TSTAT)<0.0001, then ‘<.0001’ PROBNORM(T1.TSTAT)),
IFC(1-PROBNORM(T1.TSTAT)<0.0001,"<.0001",1-PROBNORM(T1.TSTAT)))

Or how would I go about changing it to more of a series of ifs and then?

Re: Replacing Excel Formula in SAS

Reeza — Wed, 20 Feb 2019 21:30:40 GMT

Pretty sure that code isn't correct, and I would recommend checking it thoroughly. There definitely shouldn't be any 'THEN' in it.

Re: Replacing Excel Formula in SAS

asherer — Thu, 21 Feb 2019 12:43:56 GMT

I did not mean to put it was the correct answer the code I put was not correct. I will attempt to use what you had posted.. Thank you again for your help.

p_value = probnorm(tstat);

if p_value < 0 then Display = "<0.0000";
else if p_value  < 0.0001 then Display = "<0.0001";
else if p_value < 0.001 then Display = "<0.001";
else Display = "CHECKME";
Assuming you're using a data step, the following is likely what you want. There's also a format approach that can be handy:
 
SAS also has a defined PVALUE format:, anything less than 0.0001 is coded as <0.0001 and other values show up as their calculated values. 
 
data demo;
do pvalue = 0 to 0.1 by 0.0005;
output;
end;
format pvalue pvalue12.4;
run;
@asherer wrote:
So My excel code reads
=IF(H11<0,
IF(NORMSDIST(H11)<0.0001,"<.0001",NORMSDIST(H11)),
IF(1-NORMSDIST(H11)<0.0001,"<.0001",1-NORMSDIST(H11)))