topic Re: Format confusion in SAS Programming

Format confusion

cgarciam80 — Wed, 24 Jun 2020 22:49:49 GMT

Please help me understand why the column ending in "BAD" formats the input ending with 00 like that. I would have expected the same results.

DATA PAY_FILE ;

LENGTH OUTSTANDING_BAL $ 15 ;

FORMAT OUTSTANDING_BAL $CHAR15. ;

INPUT OUTSTANDING_BAL $ ;

CARDS;

000000000120584

000000000005073

000000000008371

000000000001000

000000000081000

000000000009000

;

RUN;

DATA PAY_FILE;

SET PAY_FILE;

FORMAT OUTSTANDING_BAL_OK1 dollar20.2;

OUTSTANDING_BAL_OK1 = INPUT(STRIP(INPUT(CAT(SUBSTR(OUTSTANDING_BAL,1,LENGTH(OUTSTANDING_BAL)-2),".",SUBSTR(OUTSTANDING_BAL,LENGTH(OUTSTANDING_BAL)-1,2)),20.)),10.);

OUTSTANDING_BAL_OK2 = INPUT(STRIP(INPUT(CAT(SUBSTR(OUTSTANDING_BAL,1,LENGTH(OUTSTANDING_BAL)-2),".",SUBSTR(OUTSTANDING_BAL,LENGTH(OUTSTANDING_BAL)-1,2)),20.)),10.);

OUTSTANDING_BAL_BAD = INPUT(STRIP(INPUT(CAT(SUBSTR(OUTSTANDING_BAL,1,LENGTH(OUTSTANDING_BAL)-2),".",SUBSTR(OUTSTANDING_BAL,LENGTH(OUTSTANDING_BAL)-1,2)),20.)),DOLLAR20.2);

RUN;

Re: Format confusion

Tom — Wed, 24 Jun 2020 22:59:57 GMT

Can you explain what you are trying to do?

Re: Format confusion

Tom — Wed, 24 Jun 2020 23:10:16 GMT

Do you know what the the decimal part of an INFORMAT does?

Example:

DATA PAY_FILE ;
  LENGTH OUTSTANDING_BAL $ 15 ;
  INPUT  OUTSTANDING_BAL $ ;
CARDS;
000000000120584
00000000005073
0000000008371
000000001000
0000081000
00009000
123.45
;

data test;
  set pay_file;
  value0 = input(outstanding_bal,15.);
  value1 = input(outstanding_bal,15.1);
  value2 = input(outstanding_bal,15.2);
run;

proc print;
run;

Obs    OUTSTANDING_BAL       value0     value1      value2

 1     000000000120584    120584.00    12058.40    1205.84
 2     00000000005073       5073.00      507.30      50.73
 3     0000000008371        8371.00      837.10      83.71
 4     000000001000         1000.00      100.00      10.00
 5     0000081000          81000.00     8100.00     810.00
 6     00009000             9000.00      900.00      90.00
 7     123.45                123.45      123.45     123.45

Re: Format confusion

cgarciam80 — Wed, 24 Jun 2020 23:13:45 GMT

The input is a character field with length 15. (It comes from an external source but I created it here in order to ask my question).

My goal was to add a decimal point before the last to digits of the character field and made that dollar amount. It worked for most of the fields but those ending in "00" did not work. I realized I could have done it differently but I am still curious about why it did not work.

Re: Format confusion

cgarciam80 — Wed, 24 Jun 2020 23:14:29 GMT

My goal was to add a decimal point before the last to digits of the character field and made that a dollar amount. It worked for most of the values but those ending in "00" did not work. I realize I could have done it differently but I am still curious about why it did not work.

Re: Format confusion

Shmuel — Thu, 25 Jun 2020 00:19:45 GMT

You could either read the variable OUTSTANDING_BAL using informat 15.2 instead $

DATA PAY_FILE ;
    /* LENGTH OUTSTANDING_BAL $ 15 ; */
    FORMAT OUTSTANDING_BAL 15.2 ;
     INPUT  OUTSTANDING_BAL 15.2;
     CARDS;
     000000000120584
     000000000005073
     000000000008371
     000000000001000
     000000000081000
     000000000009000
     ;
RUN;

or you can repair it by:

Data pay_file;
   OUTSTANDING_BAL_OK = input(OUTSTANDING_BAL, 15.2);
run;

Re: Format confusion

Tom — Thu, 25 Jun 2020 00:31:02 GMT

@cgarciam80 wrote:

My goal was to add a decimal point before the last to digits of the character field and made that a dollar amount. It worked for most of the values but those ending in "00" did not work. I realize I could have done it differently but I am still curious about why it did not work.

Let's deconstruct your nested function calls:

len=LENGTH(OUTSTANDING_BAL);
str1=SUBSTR(OUTSTANDING_BAL,1,len-2);
str2=SUBSTR(OUTSTANDING_BAL,len-1,2);
str3=CAT(str1,".",str2);
num1=INPUT(str3,20.);
str4=STRIP(num1);
OUTSTANDING_BAL_BAD = INPUT(str4,DOLLAR20.2);

Note that this might add spaces into STR3 depending on how long STR1 and STR2 get defined. Change CAT() to CATS() to prevent that.

So your big mistake is added those last two steps. NUM1 is the number you want. By first converting it to a string and back to a number again you have introduced a point where there can be changes. First the default conversion SAS will do when convert NUM1 into a character string so that the STRIP() function can operate on it is that it will use BEST12 to format the number. So if the value needs more than 12 characters you will loose precision. But second for integer values it will NOT insert an explicit decimal point into the string, so the implied decimal point of the DOLLAR20.2 informat will end up dividing any whole numbers by 100.

Are you just trying to create another text string? Or create a numeric variable? If you want to create a string use the STR3 value with period inserted before the last two characters. If you want to create a numeric variable then use the INPUT() function with the 20.2 informat.

OUTSTANDING_BAL_BAD = INPUT(OUTSTANDING_BAL,20.2)

That .2 means that SAS should assume the decimal point is before the last two characters, when there is not one in the string being read. That is it divides such values but 10**2.

If the string OUTSTANDING_BAL can contain commas and dollar signs you could use COMMA20.2 informat instead. Note that the DOLLAR informat is just an alias for the COMMA informat.

Re: Format confusion

Kurt_Bremser — Thu, 25 Jun 2020 08:30:21 GMT

Both of your codes are an example for overcomplicating things, and how that ends with shooting oneself in the foot. The following leads to the exact same result, with a minimum of effort:

data PAY_FILE;
input OUTSTANDING_BAL $15.;
cards;
000000000120584
000000000005073
000000000008371
000000000001000
000000000081000
000000000009000
;

data PAY_FILE_want;
set PAY_FILE;
format OUTSTANDING_BAL_num dollar20.2;
OUTSTANDING_BAL_num = input(OUTSTANDING_BAL,15.2);
run;

See the first quote of Maxim 37.