topic Re: proc sql datetime column returns 0 in SAS Enterprise Guide

proc sql datetime column returns 0

DorukK — Wed, 12 Jan 2022 11:17:22 GMT

AuditInformation_CreatedDate
2021-11-15 08:35:04.4351830
2021-11-15 08:35:04.4351830
2021-12-17 06:21:12.1300279
2021-09-26 16:54:14.0000000
2021-09-14 18:39:24.0000000
2021-09-06 12:54:52.0000000
2021-09-04 19:04:44.0000000
2021-09-04 18:33:52.0000000
2021-09-15 20:42:22.0000000
2021-10-02 17:30:20.0000000
2021-09-21 18:23:30.0000000

This is my datetime column in mssql. I would like to access AuditInformation_CreatedDate column in my customer table from EG and when I select the output looks like this:

proc sql outobs=22;

select put(AuditInformation_CreatedDate, 27.) from &sas_db_libref_idmap..Customer;
quit;

SAS Output

0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

proc sql outobs=22;

select AuditInformation_CreatedDate from &sas_db_libref_idmap..Customer;
quit;
SAS Output
AuditInformation_CreatedDate
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000

How can I solve this issue? Why does EG get my date column as 0?

Re: proc sql datetime column returns 0

PaigeMiller — Wed, 12 Jan 2022 11:41:38 GMT

If these values in the database are truly numeric date/time values, you shouldn't need to do any PUT or INPUT, you just need the proper format.

select AuditInformation_CreatedDate format=datetime24. from &sas_db_libref_idmap..Customer;

But if they are character values, that won't work. So, are they numeric or are they character? What does PROC CONTENTS say about this variable in &sas_db_libref_idmap..customer?

Re: proc sql datetime column returns 0

DorukK — Wed, 12 Jan 2022 11:46:33 GMT

Here is the script and output for proc contents:

proc sql outobs=22;
create table _Temp_ as
select AuditInformation_CreatedDate from &sas_db_libref_idmap..Customer;
quit;

proc contents data=work._temp_;
run;

This is the data type in the database:

Re: proc sql datetime column returns 0

PaigeMiller — Wed, 12 Jan 2022 13:20:53 GMT

So does the solution I gave help?

You shouldn't even need to apply a format here, as it is already formatted.

Re: proc sql datetime column returns 0

DorukK — Wed, 12 Jan 2022 13:25:03 GMT

In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960

Re: proc sql datetime column returns 0

PaigeMiller — Wed, 12 Jan 2022 13:31:33 GMT

You said the values in the database are, for example

2021-11-15 08:35:04.4351830

Is that correct? So if you do a PROC PRINT directly from the database, what do you see?

proc print data=&sas_db_libref_idmap..Customer(obs=10);
    var AuditInformation_CreatedDate;
run;

Re: proc sql datetime column returns 0

DorukK — Wed, 12 Jan 2022 13:47:22 GMT

Yes, the values are like 2021-11-15 08:35:04.4351830 value in the database.

Here is the output for the script:

proc print data=&sas_db_libref_idmap..Customer(obs=10);
var AuditInformation_CreatedDate;
run;

Re: proc sql datetime column returns 0

PaigeMiller — Wed, 12 Jan 2022 13:51:20 GMT

Ok, make a screen capture the actual database and show us the first 10 rows or so.

Re: proc sql datetime column returns 0

DorukK — Wed, 12 Jan 2022 14:37:42 GMT

Re: proc sql datetime column returns 0

PaigeMiller — Wed, 12 Jan 2022 14:50:11 GMT

Okay, this is not making sense to me. Maybe others have some idea. @Reeza @Tom @Kurt_Bremser @ballardw @Ksharp

Re: proc sql datetime column returns 0

Tom — Wed, 12 Jan 2022 14:56:31 GMT

How did you connect to the database?

What ENGINE are you using? ODBC? Something else?

What driver version are you using for the connection?

Re: proc sql datetime column returns 0

ballardw — Wed, 12 Jan 2022 17:08:55 GMT

@DorukK wrote:

In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960

This likely indicates that somewhere in the process you have a value extremely close to if not exactly zero.

I might suggest running this and showing the result:

proc sql outobs=22;

select put(AuditInformation_CreatedDate, best16.) from &sas_db_libref_idmap..Customer;
quit;

The 27. format would round anything between -0.5 and 0.5 to zero, so your format choice may be hiding something.

If you still see zero then that is the value sent across from your connection to the external data base. At that point I would ask how you know the values you are showing with the 01Jan1960 date portion are from the same records as the dates you show in the first post? Suggest that you consider looking at some identification variables and make sure you are comparing the same records.

I am guessing that your picture of the variable properties in the external database that has a "not null" as part of the description means that there must be something in the field. So you may just possibly find out that 0 (zero) is a default date when a more interesting value is not yet assigned such as an Audit has not been created yet. You might check to see if the data set is sorted by that value.