topic Remove all decimals in New SAS User

Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:44:37 GMT

Hi guys, I have a dataset with a column (i.e., a variable) with some values having .0. The decimal is always 0. I would like to remove .0. To do this, I used the following code: diagnosis_corrected = compress(DIAGNOSIS,'.'), but unfortunately values like 0726 become 726 and I would like they remain 0796. Like this, there are other values starting with 0. How can I remove all decimals while preserving cases like 0796?

data DB;
input ID :$20. DIAGNOSIS $20.;
cards;
0001 234.0
0001 4321.0
0002 V00080
0002 254
0003 876
0003 V98.0

0004 0796

0004 0543
run;

Note: the variable is a character variable

Thank you very much

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 13:34:08 GMT

If you want to remove the decimals, simply assign a format like 8.0 to the variable. This assumes the variable is numeric, and I'm getting the impression that maybe its not numeric, it is character. Which is it?

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 13:52:13 GMT

Yes, it is char. I will edit my post.

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 13:53:41 GMT

Convert the character string to actual numbers, then assign a format like 8.0.

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 13:55:46 GMT

I cannot. The variable must be char consistently with another dataset

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 14:04:23 GMT

@NewUsrStat wrote:
I cannot. The variable must be char consistently with another dataset

I don't know what this means. You can work with numbers as numbers and then convert back to character to be consistent with another data set. But I don't even understand why someone has set up a system of data where numbers are characters, unless these are some sort of ID values, and if these are ID values, how did they get the .0 on the end?

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:07:34 GMT

So, basically there are the values of that variable that could be also like: V0008. This is why it should remain char.

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 14:25:27 GMT

I'm not sure I understand. Your explanations are very incomplete. Are you saying that you have a V0008.0 and you have to remove the .0 ??? Or are you saying something else?

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:38:09 GMT

Yes, all can be: V0008.0 or sometimes V0012 (without .0), values like the ones I showed: 234.0, 4321.0 but also values without the decimal like: 654, 764.

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 14:43:58 GMT

Use the TRANWRD function

newvariable = tranwrd(oldvariable,".0","");

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:45:09 GMT

I edited the post!

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:51:17 GMT

Thank you very much for your help. I tried with "tranwrd" but unfortunately 0796 becomes 796 while it should not change.

Re: Remove all decimals

PaigeMiller — Wed, 24 Jul 2024 14:52:40 GMT

Please, when you try something and it doesn't work, don't say "it doesn't work" and stop there, with no other information provided. Please, always show us the code. Please always show us the output.

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 14:56:37 GMT

No, sorry it worked at the end. Thank you very much!

Re: Remove all decimals

Tom — Wed, 24 Jul 2024 15:04:24 GMT

Looks like you are trying to clean up some old ICD-9 diagnosis codes?

The COMPRESS() function call you showed would have only removed the periods. It would not have removed the leading zero, like you showed.

Are you saying that you also need to convert strings that are too short to be valid ICD-9 code be assuming they are missing a leading zero and add it back?

Note that it is not uncommon to see invalid ICD-9 codes appear in real world data. Data entry is hard. And some hospital system might even be consistent at inventing their own variations on valid codes, like add the extra zero at the end on a code that does not actually need the extra zero.

Since there are only a finite number of valid ICD-9 codes it might be easier to make a table with all of the valid codes and also add in every misspelling you have found and determined how to map. For example you might do it by making a format (or even an informat) and use that to help you both clean and detect new strange codes.

For example is 0543 supposed to be:

054.3 Herpetic meningoencephalitis

or perhaps one of these two?

534 Gastrojejunal ulcer
534.0 Acute gastrojejunal ulcer with hemorrhage

Re: Remove all decimals

NewUsrStat — Wed, 24 Jul 2024 16:50:18 GMT

Thank you very much Tom for sharing your expertise about this issue. I think that creating a sort of dictionary is really time-consuming and requires a lot of effort.