topic Re: text to numeric in SAS Programming

text to numeric

HeatherNewton — Thu, 08 Feb 2024 05:09:10 GMT

hi I have a file with columns and I know that they are string and the length

so I read them in and for those columns that is decimal

I will have to format it to say 4., 10.2 etc

my questions is I know the schema from db2 where the text file is converted from

so suppose I will use db2 schema for decimal(17,2)

to format these numeric column

so if in db2 these schema is decimal(17,2)

I will format this column like this:

format col 17.2

Re: text to numeric

Tom — Thu, 08 Feb 2024 05:28:27 GMT

And what does DB2 mean by the data TYPE of DECIMAL(17,2)?

Does that mean 17 decimal digits two of which are after the decimal point?
Or 19 decimal digits, two of which are after the decimal point?

If the fields in your DB2 file actually had numbers larger than 90,071,992,547,409.92 (which is not quite a full 16 decimal places) then you cannot store such a number exactly in ONE numeric variable in SAS. That is because SAS uses floating point binary numbers.

354  data _null_;
355   max = constant('exactint');
356   put max= 32. max= comma32. ;
357  run;

max=9007199254740992 max=9,007,199,254,740,992

What type of numbers are stored in that field? Is it using two decimal places because it is supposed to represent dollars? If so then to store the values exactly you will want to multiply by 100 so you do not have decimal fractions. A fraction like one tenth (0.10) cannot be stored exactly using base 2 floating point numbers.

In SAS you use a FORMAT to tell it how to PRINT the values. For numbers the format width counts all of the characters, not just the digits, including any decimal point or negative sign.

Assuming the you don't mind some small lose of precision (because of values that are too large or values like one tenth that cannot be stored precisely) to print a number with 19 digits you would need to use a format with a width of 20 when you want to include a decimal point. So use format of 20.2. And you might need a width of 21 if you could have really large negative values since the - used to indicate a negative value will take up one character position also.

Re: text to numeric

Kurt_Bremser — Thu, 08 Feb 2024 09:23:05 GMT

You say "file", but you also say "DB2". So do you read a file that was extracted from the database, or do you use a direct connection to the database via SAS/ACCESS?

If you read from a text file, open it with an editor and copy/paste a few lines into a window opened with the </> button.

Re: text to numeric

HeatherNewton — Thu, 08 Feb 2024 12:28:27 GMT

If db2 output as string say 10.5, can I input as say 19. , i testes it is reading fine but i worry it is still character in sas dataset and cannit be used for calculation
If it read fine without error does it mean it is already recognise as numeric value?

Re: text to numeric

PaigeMiller — Thu, 08 Feb 2024 12:50:43 GMT

For those situations where you really need 17 or 18 or 19 or more digits to do arithmetic, you probably need to use PROC DS2. But do you really need to do arithmetic on these long numbers?

Re: text to numeric

HeatherNewton — Thu, 08 Feb 2024 14:01:18 GMT

I am not clear let me explain again. Say We have a table from db2. In fb2 some columns are character some are numeric value. But the table was output into a file. And colleague saud in the output file all columns are string. Since i know some are actually numeric value in db2 before output as a file. So when in sas, i read the file like below:
Input @002 apple 19. When i know apple was decimal and length is 19. And i didnt format the column as numeric again after read from infile. Now my question is,
1. would my sas program run fine? That is reading apple as numbers?
2. If it does run without error, does it mean now in column apple it is number and can be used as numbers eg for calculation ?
3. What i dont understand is my colleague say col apple is string, how does sas know if string or number as it is a text file, it does look like number.

Re: text to numeric

Tom — Thu, 08 Feb 2024 14:22:20 GMT

1) The program will run fine as long as the value for apple, and only for apple, is in the 2nd through 20th column of each line of the text file you are reading.

2) Yes. It you read using a NUMERIC informat then you have created a numeric variable.

3) Everything in a text file is a string. SAS does not know (no computer program could know) whether the text in your file is supposed to represent numbers or character strings. That is something you need to know before writing the program to read it. You should have received a description of the file that you could use to make your decision of how to read it.

And this statement you made is confused:

And i didnt format the column as numeric again after read from infile.

You do not "format the column as numeric". You can MAKE or DEFINE the column as numeric. If you defined the variable as numeric in a LENGTH statement. Or if SAS made it numeric because the first place you used it your code treated it as numeric. Then it is numeric.

A FORMAT is just instructions you can OPTIONALLY attach to a variable that describe how you want the value to PRINT. It is what determines how the values will appear when you make a new text file from your data. You cannot use a FORMAT to change the TYPE of the variable.

Re: text to numeric

HeatherNewton — Thu, 08 Feb 2024 15:39:28 GMT

Thanks Tom!!
Another qu is: lets say apple was decimal(17,2) in db2. Now i read as 19., how would it display in sas dataset?

Re: text to numeric

Kurt_Bremser — Thu, 08 Feb 2024 15:44:39 GMT

1) Maxim 4: Try It.

2) Visually compare the text file with what you get in SAS.

3) SAS doesn't know anything. YOU tell it through your code what to do.

Re: text to numeric

Tom — Thu, 08 Feb 2024 16:08:42 GMT

@HeatherNewton wrote:
Thanks Tom!!
Another qu is: lets say apple was decimal(17,2) in db2. Now i read as 19., how would it display in sas dataset?

If you don't attach any format to the variable then it will normally print like BEST12. format. (If you, for some strange reason, ask PROC SQL to print output I think it will use BEST8. instead.)

So values that are small will look like 1234.56. But large values will have to resort to use scientific notation to be able to indicate the magnitude of the value in only 12 characters.

You can just try it yourself. Just run a data step and a PROC PRINT step.

data test;
  input x 19.;
cards;
1
1234.56
1234567890123456789
;

proc print;
run;

Obs            x

 1          1.00
 2       1234.56
 3     1.2346E18

Re: text to numeric

HeatherNewton — Thu, 08 Feb 2024 21:28:50 GMT

If apple is actually date in format 2022-11-30, can i input straight away using date informat?

Eg from infile
Input @002 apple yymmdd10.;

Or i really have read with infirmat $10.
Then do input(apple,yymmdd10.) then format apple date9.?

Re: text to numeric

SASKiwi — Thu, 08 Feb 2024 21:50:54 GMT

Why not just try it for yourself? ALL date strings can typically be read directly into SAS dates as long as there is an INFORMAT to handle it.

data Want;
  input @1 apple yymmdd10.;
  format apple date9.;
  put apple=;
cards;
2022-11-30
;
run;