topic Re: Convert character to numeric is marking 0.0 as missing in SAS Programming

Convert character to numeric is marking 0.0 as missing

mariko5797 — Tue, 02 Nov 2021 16:06:21 GMT

I have to variables with $6. informat/format. I want to convert this 0.0 character to 0.0 numeric. This is just so I can use it for math later on.

I am using the following code:

eryth_ = input(erythema, 3.1);

But it is recording informat as best12. and displaying 0.0 as missing (.) Is there a way to fix this?

Re: Convert character to numeric is marking 0.0 as missing

Reeza — Tue, 02 Nov 2021 16:09:56 GMT

Show your full code and log.

Re: Convert character to numeric is marking 0.0 as missing

Tom — Tue, 02 Nov 2021 16:15:57 GMT

Why are you using .1 on the INFORMAT? Do you have strings where the decimal point was explicitly not include? If not then if you have any strings (integers) where the string does not include a period the value will be divided by 10.

Why are you using a width of 3 when the variable has 6 bytes? Note that INPUT() function does not care if the informat width is larger than the length of the expression being read. So just use the maximum width the informat supports.

eryth_ = input(erythema,32.);

Re: Convert character to numeric is marking 0.0 as missing

mariko5797 — Tue, 02 Nov 2021 16:16:19 GMT

I did try outside of the MERGE data step and end up with the same results.

496  data zrll_;
497   merge  pain (rename= (COL1 = pain))
498          tender (rename= (COL1 = tender))
499          erythema (rename= (COL1 = erythema))
500          swellsev (rename= (COL1 = swellsev))
501          swell (rename= (COL1 = swell));
502   by patid dosenum;
503
504   if _name_ = 'ZRLSWMA' then day = '1';
505      else if _name_ = 'ZRLSWMB' then day = '2';
506      else if _name_ = 'ZRLSWMC' then day = '3';
507      else if _name_ = 'ZRLSWMD' then day = '4';
508      else if _name_ = 'ZRLSWME' then day = '5';
509      else if _name_ = 'ZRLSWMF' then day = '6';
510      else if _name_ = 'ZRLSWMG' then day = '7';
511      else if _name_ = 'ZRLSWMH' then day = '8';
512      else if _name_ = 'ZRLSWMO' then day = 'O';
513
514   ***NEED TO UPDATE: 0.0 marked as missing***;
515   pain_ = input(pain, 1.);
516   tender_ = input(tender, 1.);
517   eryth_ = input(erythema, 3.1);
518   swellsev_ = input(swellsev, 1.);
519   swell_ = input(swell, 3.1);
520
521   drop _label_ _name_ pain tender erythema swellsev swell;
522  run;

NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: There were 1458 observations read from the data set WORK.PAIN.
NOTE: There were 1458 observations read from the data set WORK.TENDER.
NOTE: There were 1458 observations read from the data set WORK.ERYTHEMA.
NOTE: There were 1458 observations read from the data set WORK.SWELLSEV.
NOTE: There were 1458 observations read from the data set WORK.SWELL.
NOTE: The data set WORK.ZRLL_ has 1458 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.09 seconds

Re: Convert character to numeric is marking 0.0 as missing

Tom — Tue, 02 Nov 2021 16:26:23 GMT

So you are reading the output of PROC TRANSPOSE?

If the original PROC TRANSPOSE only included numeric variables then COL1 is already numeric then there is no need for the INPUT() function call. So just give COL1 the name you want for the numeric variable.

 pain (rename= (COL1 = pain_))

If the original PROC TRANSPOSE included both numeric and character variables then the text generated for the numeric variables will be RIGHT aligned in the new character COL1 variable. So you will want to use LEFT() or STRIP() to remove the leading spaces.

To handle both situations you can use the CATS() function instead as it will convert both numeric and character variables to character. The INPUT() function does not care if the width used on the informat is larger than the length of the string being read. Also do not include decimal places on an informat unless you know that the strings have purposely removed the decimal point to save one byte of storage. Using an decimal value D means you want SAS to divide strings without periods by 10**D. So using 4.2 to read "1234" results in 12.34 instead of 1,234.

Try code like this:

pain_ = input(cats(pain), 32.);

Re: Convert character to numeric is marking 0.0 as missing

mariko5797 — Tue, 02 Nov 2021 16:48:55 GMT

Everything was stored as character. I think this is just a standardization thing, but it means I need to convert a lot character variables to other formats.

I misunderstood how the informat worked. I took it as it would always have one decimal place, not insert a decimal one digit in (i.e. divide by 10).

How do cats() and strip() differ? When I looked up the documentation for cats(), it said it removes both leading and trailing blanks. But doesn't strip() do the same thing?

Re: Convert character to numeric is marking 0.0 as missing

Reeza — Tue, 02 Nov 2021 16:53:04 GMT

CATS, with a single value. is the same as STRIP. But if you pass more values to CATS, it strips them AND concatenates them.
STRIP only removes trailing/leading blanks.

CATS(variable) = strip(Variable)
CATS(variable1, variable2) ne strip(variable1, variable2) (will not work).

Re: Convert character to numeric is marking 0.0 as missing

Tom — Tue, 02 Nov 2021 17:39:23 GMT

STRIP() works only on character strings and takes only one argument. If you try to run it on a numeric variable then SAS's automatic numeric to character conversion is triggered.

CATS() can work on either numeric or character variables. For numeric variables it uses its own conversion from numeric to character and does not trigger the SAS default numeric to character conversion process (nor the notes in the LOG). (The conversion logic is shared with all of the other CATxxx series of functions). Plus it accepts an unlimited number of arguments instead of just one.

Re: Convert character to numeric is marking 0.0 as missing

Tom — Tue, 02 Nov 2021 17:41:13 GMT

Are you sure you want to MERGE those datasets?

NOTE: MERGE statement has more than one data set with repeats of BY values.

Perhaps you want to use SET instead of MERGE so that the observations are interleaved?

Or you need to include more variables into the BY statement so that the observations are identified uniquely?