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?
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?
So does the solution I gave help?
You shouldn't even need to apply a format here, as it is already formatted.
In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960
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;
Ok, make a screen capture the actual database and show us the first 10 rows or so.
Okay, this is not making sense to me. Maybe others have some idea. @Reeza @Tom @Kurt_Bremser @ballardw @Ksharp
@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.
How did you connect to the database?
What ENGINE are you using? ODBC? Something else?
What driver version are you using for the connection?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.