topic Re: Divide by 100% in SAS Enterprise Guide

Divide by 100%

SVoldrich — Mon, 14 Sep 2020 21:24:41 GMT

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%

Re: Divide by 100%

ballardw — Mon, 14 Sep 2020 21:44:08 GMT

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.

Re: Divide by 100%

SVoldrich — Tue, 15 Sep 2020 10:45:31 GMT

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.

Re: Divide by 100%

SVoldrich — Tue, 15 Sep 2020 11:03:38 GMT

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.