topic Re: How to convert this number to character 0.9976452119. Put function is giving me 1 in SAS Programming

How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Sat, 16 Jul 2022 03:14:42 GMT

I have a percent field that I need to convert and keep all the decimal points. Following code is converting my value from 0.9976452119 to 1 when I use PUT function. for some fields when I convert from numeric to char it is returning blank values.

Proc sql;

create table test as

select pid, put(percent_s,30.) as percent_char;

from test;

quit;

Any suggestions?

Thank you

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Tom — Sat, 16 Jul 2022 03:54:36 GMT

That is what a format with zero decimal places is supposed to do.

You could tell it how many decimal places to use.

Or you could try the BEST format instead and SAS will decide how many decimal places the value needs.

2758  data _null_;
2759    x=0.9976452119;
2760    put x= / x= 30.20 / x= best30. ;
2761  run;

x=0.9976452119
x=0.99764521190000000000
x=0.9976452119

You could also use the CATS() function as that will convert a number using something very similar to the BEST32. format. It also has the advantage of removing the leading spaces from the generated string. With the PUT function you might need to use LEFT() function or the -L format modifier to remove the leading spaces from the generated string.

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Mon, 18 Jul 2022 17:47:09 GMT

Used Best30. and it worked.
Thank you

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Tue, 19 Jul 2022 14:34:32 GMT

Just realized some of my values are converting in a wrong format when I used put(temp1,Best30.)

Actual value:

2022-06-07 16:02:59.170093000

output value:

1973779822.69097

so used the following, still not returning the correct values..

put(temp1, datetime16.)

How can i read Actual value completely with out changing the format?

Thank you

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Tom — Tue, 19 Jul 2022 14:20:35 GMT

BEST does not change the value, just how to display it as characters.

Since SAS stores datetime values as number of seconds since 1960 the value for the datetime you mention about 1,970,236,979.

5    data _null_;
6     dt = '07JUN2022:16:02:59.170093000'dt;
7     put dt best30. ;
8    run;

              1970236979.17009

If you actually got a value of 1,973,779,822 then you are over a month off.

1    data _null_;
2     dt = 1973779822.69097 ;
3     put dt datetime26.5 ;
4    run;

  18JUL2022:16:10:22.69097

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Tue, 19 Jul 2022 18:22:03 GMT

Thank you Tom.

datetime26. worked but I need the output in the following format

2022-07-18 16:10:22.690972090

NOT

18JUL2022:16:10:22.690972090

Is there any simple way?

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Tom — Tue, 19 Jul 2022 18:47:52 GMT

@Stalk wrote:

Thank you Tom.

datetime26. worked but I need the output in the following format

2022-07-18 16:10:22.690972090

NOT

18JUL2022:16:10:22.690972090

Is there any simple way?

I don't think there is an existing format that displays datetime values in the way. You could look at trying to create one using the PICTURE statement of PROC FORMAT.

Or build the string in pieces, but note that you cannot store that many decimal places on a number that large. SAS can only represent about 15 decimal digits exactly.

data _null_;
 dt = '18JUL2022:16:10:22.690972090'dt;
 string=put(datepart(dt),yymmdd10.)||' '||put(int(dt),tod8.)||put(mod(dt,1),10.9);
 put dt datetime26.5 / string ;
run;

Results

25   data _null_;
26    dt = '18JUL2022:16:10:22.690972090'dt;
27    string=put(datepart(dt),yymmdd10.)||' '||put(int(dt),tod8.)||put(mod(dt,1),10.9);
28    put dt datetime26.5 / string ;
29   run;

  18JUL2022:16:10:22.69097
2022-07-18 16:10:22.691000000

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Tue, 19 Jul 2022 21:44:50 GMT

I am reading from one database and writing to another database so I cannot change a single digit or format in my conversion.

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Tom — Tue, 19 Jul 2022 22:56:08 GMT

@Stalk wrote:

I am reading from one database and writing to another database so I cannot change a single digit or format in my conversion.

If you are using SAS to read or write from an external database then there should be no need to re-format the values at all. If the variable is DATETIME in the external database then the SAS/Access engine for that type of data will transfer the values to a SAS datetime value. And the reverse.

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Wed, 20 Jul 2022 00:51:10 GMT

I am using ODBC connection to read from external DB and writing to Excel templates. have 15 fields. Most of them are string fields, two fields are double and one field is timestamp.

libname HD odbc dsn='HD_V';

data Hadoop_table;
set HD.RPM ;
run;

How can I keep the original format for the three non-string fields? especially the timestamp field where SAS is rounding.

Re: How to convert this number to character 0.9976452119. Put function is giving me 1

Stalk — Wed, 20 Jul 2022 18:29:37 GMT

Heard that SAS/Access to Hadoop only work on 9.4M7. I have 9.4M3. So I need to work around to read the data with out changing the data\formats