Checking Variables in Dataset To Verify They Are Formatted Correctly

[elwayfan446]

Hello!

I am trying to find a way to periodically validate that all data in a variable meets a certain format.  For example, I want to check to make sure all interest rates are stored as 6.5, so for a 4.75% interest rate, I want to make sure the value is being stored as .04750.  If they are being stored a different way, I want to do an update to correct them.

Is there an easy way to check this?

Thanks!

[Kurt_Bremser]

With numbers, you can check for a valid range. For interest rates, you might check for

0 lt rate le 0.25

or so.

[Tom]

You probably cannot do what you want directly because SAS formats (and the humans that read the text they create) work with decimal numbers and SAS stores data as binary numbers.

You could test if the values stored match the rounded value to the same precision.  So if you are displaying values with 2 decimal places test if X = ROUND(X,0.01) to see if there are hidden digits of precision.

If you are displaying numbers with 6.5 format then there is room for ZERO digits to the left of the decimal place.  So make sure the number is between 0 and 1.

If you are display number with 8.3 format then there is room for 4 digits to the left of the decimal place. So check if the number is between 9999 and -999 (the negative sign takes up one of the places).

[ballardw]

Are you expecting to differentiate between a value of 0.03 and 0.03000????

---

@elwayfan446 wrote:

Hello!

I am trying to find a way to periodically validate that all data in a variable meets a certain format.  For example, I want to check to make sure all interest rates are stored as 6.5, so for a 4.75% interest rate, I want to make sure the value is being stored as .04750.  If they are being stored a different way, I want to do an update to correct them.

Is there an easy way to check this?

Thanks!

---

[elwayfan446]

@ballardw 

No, but scenarios I could have on 3.00%  could be 3.00, .30000, 0.0030.... when I want 3% to be stored as .03000.

Often times when the data is inserted into the data set it is multiplied or divided by the wrong factor.  This incorrect data is what I am trying to scrub.

[ballardw]

Without additional information for something I do not see how to tell if 0.0030 is supposed to be 0.03 unless you have an absolute range limit in the value of the variable.