How do i check if my Numeric column contains Character?

[imdickson]

Hi. My script is reading data from Postgres SQL and for some reason, Double precision column in postgres with the value of "Infinity" is read in as 0.000000E309.

The thing is, the column in SAS Dataset is defined as Numeric and i have no idea how it became 0.000000E309.

For now, i would like to add a condition checking if this variable in SAS contains any alphabet or not. If yes, straight set this column to 0.

I got this logic:

if compress(LocalPeakMw," +-.0123456789") = "" then LocalPeakMw=0;
else if missing(LocalPeakMw) or LocalPeakMw=. then LocalPeakMw=0;

But no luck as i am still seeing the E309.

Can anyone guide me further?

[Tom]

I don't understand.  

0.000000E309

is a numeric value.  That is just scientific notation.

Not sure how you could tell the difference between zero and zero times ten to 309th power though.  Both are still zero.

[Astounding]

In SAS, numeric values never contain characters.  SAS uses exponential notation because the value would be too long to express otherwise.

You can get rid of the exponential notation for reporting purposes (or viewing purposes) by attaching a format to the variable.  But a better idea would be to decide what should happen.  If your database contains "infinity", what value do you want to store as a number in your SAS data set?  Once the decision is made, we can go about trying to produce the desired result.

[imdickson]

Hi Tom and Astounding. I want it to become 0 numeric and doesn't want it to be exponential permanently in this dataset as this table will be flowing to another table for another process hence I want it to be just 0. Is there a way to do so?

[Astounding]

Since we don't see the actual data, you might need to explore a little.  For example, run PROC MEANS and look at the MAX statistic.  You might end up coding something like this:

if var > 1E200 then var=0;

[Tom]

Please explain what you have.

Is the issue that you have some value in a SAS variable this is being displayed with that strange scientific notation?

Display the value using the HEX16. format to see exactly what value it contains.

For example here is what the value 100 looks like in that format.

533   data test;
534     x=100;
535     put x hex16.;
536   run;

4059000000000000

You can then test for it in your code.

548   data check;
549    set test;
550    if x=input('4059000000000000',hex16.) then put 'found';
551   run;

found
NOTE: There were 1 observations read from the data set WORK.TEST.

[Tom]

---

@imdickson wrote:
Hi Tom and Astounding. I want it to become 0 numeric and doesn't want it to be exponential permanently in this dataset as this table will be flowing to another table for another process hence I want it to be just 0. Is there a way to do so?

---

It is still not clear what you have.  Do you want to treat really small values as zero? Or is it that the value is actually really large?

if abs(x) < 1E-5 then x=0;
if abs(x) > 1E300 then x=0;

[imdickson]

@Tom @Astounding . I have over a million of records in this table that contains value from -50.0013423

to 500.75748393

What i Do not want to see is 0.0000000003E347 which is actually Infinity from PostgresSQL as this is causing issue when i flow to other table for other SAS tool.

I just want to eliminate this without affecting any other value in other row of record. 

Can i have do something like if it is exponential like above then set it to 0?

[Tom]

---

@imdickson wrote:

@Tom @Astounding . I have over a million of records in this table that contains value from -50.0013423

to 500.75748393

What i Do not want to see is 0.0000000003E347 which is actually Infinity from PostgresSQL as this is causing issue when i flow to other table for other SAS tool.

I just want to eliminate this without affecting any other value in other row of record. 

Can i have do something like if it is exponential like above then set it to 0?

---

Yes.  What did you try?

Now that you have given a little more information we can give a little clear instructions. But you still left out a lot of details.

Let's assume have a dataset named HAVE that contains a variable named VALUE which potentially has these +infinity and/or -infinity values.  Since you expect the numbers between -51 and +501 you can use that information.  For example what happens when you try this code? How many observations does the resulting dataset COUNTS have?

proc freq data=HAVE;
  where abs(value) > 1000 ;
  tables values / noprint out=counts;
run;

It the number is more than 5 to 10 then change the cut off the something larger than 1000.  You count even set the limit to something like 1E300 and it should still include the value of 0.0000000003E347 that you appear to have.

Once you have output of the PROC FREQ step down to just a few observations then you can just print them and look at them what values are actually in the data.  The COUNT variable from PROC FREQ will show how often they appear.

proc print data=counts;
  format value hex16. ;
run;

That way you should be able to see the actual values that you variable contain instead of the approximation that SAS is showing when trying to display the goofy number as normal digits.

Then you now have two ways to handle the goofy values.  You can use the same range based method used above the WHERE clause.  Or you can use the exact method.  You can convert the values to zero, missing or one of more of the special missing. 

data want ;
  set have ;
  if abs(value) > 1000 then value=.;
run;