Divide by 100%

[SVoldrich]

Hi!

My dataset has 2 values - TotalDollars and PennetrationRate. To get a total $ of all to be considered, I'm having sas do this formula in the proc sql

TotalDollars / PennetrationRate as TotalToBeConsidered.

What I'm expecting is 10.4/.9 = 11.56 - and i get this no problems if the pennetration rate is less than 100%

when the PennetrationRate=1 (aka 100%)

10.4/1 = 10.4 and instead i'm getting 104

Importing from excel. 
PennetrationRate is importing as char(4) format=$4. TotalDollars is importing as char(8) format=$8.

in the raw data the Pennetration rate looks like 1.
i'm using input in the formula.

(input(TotalDollars,Dollar15.10)/input(PennetrationRate,PERCENT12.1))  as TotalToBeConsidered

I've also tried with 

(input(TotalDollars,Dollar15.10)/input(PennetrationRate,BEST12.1))  as TotalToBeConsidered 

Get the same result of *10.

Any thoughts as to why it's doing that?

works beautifully on anything other than 100%

[ballardw]

Please show some explicit values.

Almost certainly you should not be using a decimal portion when importing with PERCENT.

Please run this code and examine the result.

data example;
   input z $;
   p = input (z,percent12.1);
   p2 = input(z,percent12.);
datalines;
%100
%96
%5
;

The numeric "percent" value for p on the first line ends up as 0.1, not 1.

When you use a format like percent12.1 then you are forcing the value to be read with a decimal inserted into the string before conversion. This is an IMPLIED decimal, meaning you may well have told SAS to treat the value as smaller than you think. So instead of dividing by 1 you are actually dividing by .1.

[SVoldrich]

What I'm expecting is 10.4/.9 = 11.56 - and i get this no problems if the penetration rate is less than 100%

when the PenetrationRate=1 (aka 100%)

10.4/1 = 10.4 and instead i'm getting 104

PenetrationRate is importing as char(4) format=$4.

TotalDollars is importing as char(8) format=$8.

So are you saying i should do this?

(input(TotalDollars,Dollar15.10)/input(PenetrationRate,12.1))  as TotalToBeConsidered 

Even with this i get a *10 output.

[SVoldrich]

This solves it.

PenetrationRate is importing as char(4) format=$4.

TotalDollars is importing as char(8) format=$8.

(input(TotalDollars,Dollar15.10)/input(PenetrationRate,Comma32.))  as TotalToBeConsidered 

What I'm expecting is 10.4/.9 = 11.56  if the penetration rate is less than 100%

10.4/1 = 10.4 if the penetration rate is 1.