Date from SAS showing 1/1/1960

[Anotherdream]

Hiya Fleroo. Sorry I was not clear enough when I said dhms(yourdate,0,0,0); I assumed that you already knew about date literals and the fact that general dates were stored as numbers (so you can't pass a date macro into it without the literal).

You know what they say about assumptions!

But yeah, in the long run, spend some time looking up how dates are stored within sas (and all systems) and understanding things like "formatting does not change date values etc" and it will greatly benefit your knowledge of not just sas, but all coding languages.

I see many new users have this exact problem when not recognizing the difference between datetimes and dates!

Glad we could all help

[Reeza]

You're mixing open code and macro code, plus have a colon instead of semicolon.

hos_date = dhms("&curdate"d,0,0,0): can't be written as is, unless its in a datastep.

You don't need it though, can put it straight into your proc sql statement:

proc sql;

insert into safety.safety_hrs_svc

select dhms("&curdate"d,0,0,0) as hos_date, STATION, CONTR_ID, CONTR_NAME, PRIM_SVC_AREA_ID, DRIVER_NBR, DRIVER_NAME, PCK_DELV_HRS, SPOT_HRS, LINEHAUL_HRS from work.safety_hrs;

quit; 

[LarryWorley]

We still are not seeing how the value is being stored in your work data set.  Need to see the value of hos_date, in addition to currdate.  Try adding the following statements to the data step:

     put hos_date= ;    * uses data set format of hos_date ; 

     put hos_date= date9. ; * format as a date;

     put hos_date=  16.14 ;  * format as number not date ;

Also please run a proc contents on your working data set and look at the format associated with hos_date.  If there is no format, you may need to add a date format so that SAS knows what to do when it writes the data it sends to oracle.

Also run proc contents on the oracle table, Perhaps it has hos_date formatted as a string and not a date value.In which case, you would need to store currdate as a string in the work data set.

[jakarman]

Hi,
All information on how working with dates are correct.

- All kind of tools like SAS Excel Oracle MYSql are using different conventions.

- SAS is using a numeric format counting days (seconds).

  The literal conversion macro to SAS variabel is done by the '...'d   (literlal representing date convention) all found in LRCON.

The more tricky parts:

- You need formats (output) and informats connected to the variable (format statement or attribute statement) by that all SAS processing will know it.
A common habit is explicitly coding formats while just using the put statement. However this bypasses metadata information in the SAS table-header. Use proc contents to list this kind of information in libraries.

Translations (automatic):

When using a SAS/Access interface it will use the Oracle part (DML already existing tables, commonly a DBA has defined the table lay-out DDL)
and the SAS table header information to the necessary translate and convert.  See:

SAS/ACCESS(R) 9.4 for Relational Databases: Reference chapter Data Types for Oracle""
The Date type Oracle and SAS Datetime type (measurement Seconds) are equivalent

Overruling an automatic conversion is also possible by using DBTYPE and SASDBTYPE as options.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference (DBTYPE)

..==..

[Tom]

Oracle does not have a DATE data type. It stores dates as DATETIME and suppresses the time when displaying the value.

When SAS inserts into Oracle tables it does not always (or is that never?) verify that you aren't inserting apples into the oranges field.

Either convert the variable in your SAS table to a DATETIME value ( "&CURDATE:00:00"dt ) or add code in the INSERT statement to convert the DATE value to a DATETIME value (select dhms(HOS_DATE,0,0,0) ... ).

[Fleroo]

Well, sorta/kinda Tom .  Oracle's Datatype indeed is called DATE (in which you are correct, the Time is included in that).  There is an entire gammit of Oracle Functions that let you format this Date Tatatype how you like.  Oracle does display the time when you Select the Date datatype.  It is up to the user to eliminate the date by using something like the TRUNC() Function, which eliminates displaying the Time portion of a Date Datatype.

[araz]

Hi All, 

I know that this issue is completely resolved, but let me offer a solution that might shed lights on future problems of sort:

I had an oracle table (in a library called CRM)  that I needed to insert some records using SAS . 

This is how I proceeded:

first: got the data strucure of the table: 

1) proc sql; describe table crm.open;

answer in log: 

create table open 
(
EVENT_TYPE_ID num label='EVENT_TYPE_ID',
ACCOUNT_ID num label='ACCOUNT_ID',
LIST_ID num label='LIST_ID',
RIID num label='RIID',
CUSTOMER_ID num label='CUSTOMER_ID',
EVENT_CAPTURED_DT num format=DATETIME22.3 informat=DATETIME22.3 label='EVENT_CAPTURED_DT',
EVENT_STORED_DT num format=DATETIME22.3 informat=DATETIME22.3 label='EVENT_STORED_DT',
CAMPAIGN_ID num label='CAMPAIGN_ID',
LAUNCH_ID num label='LAUNCH_ID',
EMAIL_FORMAT char(1) format=$1. informat=$1. label='EMAIL_FORMAT'
);

2) As you see, I have two variables that are of DateTime type, as Oracle keeps date in this formt.

So I create tmy macro variable using DateTime function in SAS: 

%Let date_time=%sysfunc(compress(%sysfunc(datetime(),DATETIME22.3)));

 %let date=%sysfunc(date(), date9.);

3) Proceed to insert values, but make sure to use "dt" instead of "d" after macro variable.

proc sql;
insert into open2
values(4,3333,1111,.,&customer_id,"&date:00:00"dt,"&date_time."dt,1111,1111,'H');
insert into click2
values(5,3333,1111,0,&customer_id,"&date:00:00"dt,"&date_time"dt,1111,1111,'H','AAAAAA',0,'AAAAAA','AAAAAA');
;
quit;

Conclusion: 

Even if you create correct date format as is the case with my macro variable date_time, you need to use it as "&date_time"dt to be able to insert in a right format.

If you use "&date_time"d instead, you will get incorrect date , which is 01JAN1960:05:39:21.000 , instead of 30SEP2015:14:17:40.041, which is the intended value.

And I don't think this is Oracle issue, I think it is sas issue and date time issue instead of date. Only in sas it reverts to 1960, not in Oracle.

If you wnat to use date instead of datetime, then you will need to use @Tom's solution, which I have used in one of the field for matter of illustration. 

Hope this helps.

Best,

Araz