topic Re: Date from SAS showing 1/1/1960 in SAS Procedures

Date from SAS showing 1/1/1960

Fleroo — Fri, 19 Jul 2013 17:29:32 GMT

Hi, I'm a SAS user for 1 week now, so complete rookie. I am calling a SAS program from a Shell Script (Unix .ksh) and within the SAS program I am retrieving the Current Date from the shell as such:

%LET curdate=%SYSGET(CURDATE);

In the program, I am parsing out records from a .LST file as follows:

DATA safety_hrs;

INFILE getin missover firstobs=4;

INPUT

@2 STATION 4.

@12 CONTR_ID $8.

@27 CONTR_NAME $35.

@81 PRIM_SVC_AREA_ID $6.

@91 DRIVER_NBR $7.

@102 DRIVER_NAME $35.

@145 PD_HRS 5.2

@161 SP_HRS 5.2

@177 L_HRS 5.2;

I then set the Date I got from the Shell to a variable like so:

HOS_DATE=&curdate;

An Finally the select into my Oracle table as follows:

proc sql;

insert into safety.safety_hrs_svc

select 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;

THE PROBLEM.... The date shows as 1/1/1960 in the Oracle Table, yet when I use a %Put (%put &curdate;) to display it in the SAS log it indeed shows as the true date 7/19/2013 as follows:

SYMBOLGEN: Macro variable CURDATE resolves to 07/19/2013

What is the problem ? Where am I going wrong ? Thanks in advance

Re: Date from SAS showing 1/1/1960

Reeza — Fri, 19 Jul 2013 19:27:12 GMT

HOS_DATE=&curdate

That's possibly it.

In code that would resolve to

HOS_DATE=07/19/2013;

Which is equal to 7 divided by 19 divided by 2013 which is pretty close to 0 and therefore January 1, 1960.

Anyways, you should be able to use the SAS date as follows:

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

%put &curdate;

Then use

HOS_DATE="&curdate"d;

You might be able to nest it like follows if you want the system date instead of using the SAS date() function:

%LET curdate=%sysfunc(%SYSGET(CURDATE), date9.);

Re: Date from SAS showing 1/1/1960

LarryWorley — Fri, 19 Jul 2013 19:33:07 GMT

Hi Fleroo,

First some points about how both SAS and Oracle store dates. Sas stores dates as numeric value representing the number of days since 1/1/1960. I believe oracle also stores as numeric count with a different base date but I could be wrong. Also Unix does the same but again with a different reference point.

Here is what I think is happening:

1. Macro compiler: The statement HOS_DATE=&curdate; is resolved to HOS_DATE = 07/19/2013 ; by the macro compiler.

2. The data step compiler sees HOS_DATE = 07/19/2013 ; and assumes HOS_DATE is a numeric variable because it does not see a quoted literal. It then performs the arithmetic operation of 7 DIVIDED BY 19 DIVIDED BY 2013. This results in 0.0001931887.

3. When SAS is communicating to oracle, it takes its internal value and formats as a date string which Oracle can interpret. Since SAS dates are stored as the number of days since 1/1/1960, a value of 0 ==> 1/1/1960, which is what is sent to Oracle.

To fix, change your assignment statement to either:

1. HOS_DATE="&curdate"d; Note the double quotes are important to get the macro variable resolved and quoting a date-like string and appending the character d at the end tells SAS this is a date value.

2. Use built-in sas function and avoid system dependent commands:

HOS_DATE = today() ;

Note ; if you want to check what the internal value of HOS_date is in your work data set run the following code:

data _null_ ;

set work.safety_hrs;

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 ;

run ;

Some reading that might be helpful would be to look at the SAS 9.X Language Reference Concepts document for your release of SAS -- chapter 2 and 7 in particular. Here is link for 9.2 pdf version, http://support.sas.com/documentation/cdl/en/lrcon/62955/PDF/default/lrcon.pdf

Hope this helps.

Re: Date from SAS showing 1/1/1960

Fleroo — Fri, 19 Jul 2013 19:51:11 GMT

Thanks for the replies guys, but no go. I substituted my code for this

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

%put &curdate;

HOS_DATE="&curdate"d;

I displayed the date in the Sas.log file. I got the following message:

SYMBOLGEN: Macro variable CURDATE resolves to 19JUL2013

Unfortunately, when sending HOS_DATE to Oracle on the Insert, the date still came out as 1/1/1960. I'm completely puzzled at this. Something appears to be converting it on the Select or Insert Statements ?

Thx again

Re: Date from SAS showing 1/1/1960

Anotherdream — Fri, 19 Jul 2013 20:01:33 GMT

Hey Fleroo. Is your oracle data field a datetime field by chance? if so that's your problem (if you are doing a create table from sas to oracle it automates it as a datetime).

Let me clarify that I actually am going to assume it is, because in oracle the "date" field is actually a date time field (it stores the DD-MM-yy:HH:MM:SS). However when you query the results it only returns the date portion unless you tell it specifically not too (by the way, this is one of the many reasons some people HATE oracle).

Anyways, what is happening is that when sas stores a date, it actually stores a number (the number of days between 1-1-1960 and whatever date you want to display). Aka if you wanted to display 1-5-1960, it would store a 5. The issue is when you insert this into the sql server, it converts the 5 DAYS into 5 SECONDS, and your date becomes 1-1-1960:00:00:05, but again oracle only displays 1-1-1960.....

So if you want to get your process to work, wrap you date into a datetime (do this by using the dmhs function, or dhms(YOURDATE,0,0,0);

THen when you insert this into the database it will work! (if this was your problem of course!)

Sorry you're stuck with oracle

Re: Date from SAS showing 1/1/1960

LarryWorley — Fri, 19 Jul 2013 20:09:33 GMT

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.

Re: Date from SAS showing 1/1/1960

jakarman — Sun, 21 Jul 2013 20:48:39 GMT

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)

..==..

Re: Date from SAS showing 1/1/1960

Fleroo — Mon, 22 Jul 2013 14:20:52 GMT

Anotherdream, yes the date is stored in an Oracle table, and it is indeed a date/time field. I tried this........ HOS_DATE= dhms(&curdate,0,0,0); but the SAS log threw an error:

ERROR 72-185: The DHMS function call has too many arguments.

So it appears the Syntax is incorrect . All examples I see onlune are showing the date portion as a literal, for instance,

DHMS('21JAN2011'd,0,0,0);

what would be correct Syntax for my date names &curdate ?

Re: Date from SAS showing 1/1/1960

esjackso — Mon, 22 Jul 2013 14:48:13 GMT

did you try?

hos_date = dhms("&curdate"d,0,0,0):

The dhms variables are either date variables or numeric or something that converts to such like a date literal. Your posted code does not have the date literal syntax around your macro variable.

Re: Date from SAS showing 1/1/1960

Fleroo — Mon, 22 Jul 2013 14:55:24 GMT

esjacks, I just tried it and get the following error in the log:

SYMBOLGEN: Macro variable CURDATE resolves to 22JUL2013

ERROR 180-322: Statement is not valid or it is used out of proper order.

HERE IS MY COMPLETE CODE....... AS FOLLOWS

OPTIONS LINESIZE = 255 PAGESIZE = 60 FULLSTIMER symbolgen macrogen;

* filename getin "/home/odeftpp/hos.txt";

filename getin "/home/odeftpp/HOS_Safety_Combined_Hrs.lst";

* read in UNIX date (system date) variable CURDATE from a shell script and assign it as the macro variable curdate;
%let curdate=%sysfunc(date(), date9.);
%put &curdate;

hos_date = dhms("&curdate"d,0,0,0):

DATA safety_hrs;
   INFILE getin missover firstobs=4;

   INPUT
    @2   STATION          4.
    @12 CONTR_ID         $8.
    @27 CONTR_NAME       $35.
    @81 PRIM_SVC_AREA_ID $6.
    @91 DRIVER_NBR       $7.
    @102 DRIVER_NAME      $35.
    @145 PCK_DELV_HRS     5.2
    @161 SPOT_HRS         5.2
    @177 LINEHAUL_HRS     5.2;

    if PCK_DELV_HRS = . then PCK_DELV_HRS = 0.00;
    if SPOT_HRS = . then SPOT_HRS = 0.00;
    if LINEHAUL_HRS = . then LINEHAUL_HRS = 0.00;

proc print data=safety_hrs (obs=25);

run;

libname safety oracle user=tmsapp password=tmsapp
   path='dssdev02' schema=tmsown;


proc sql;
insert into safety.safety_hrs_svc
select 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;

Re: Date from SAS showing 1/1/1960

Fleroo — Mon, 22 Jul 2013 14:59:47 GMT

Granted, I'm a SAS rookie, but I can't believe how stinking hard it is to write the Current Date to an Oracle table from SAS. Unreal. :smileyplain:

Re: Date from SAS showing 1/1/1960

Reeza — Mon, 22 Jul 2013 15:15:50 GMT

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;

Re: Date from SAS showing 1/1/1960

LarryWorley — Mon, 22 Jul 2013 15:19:43 GMT

Flerro,

I find it hard to believe that you have not looked at the link to documentation i sent you. Also perhaps you need to review macro processing also and spend a little time understanding the datastep process in SAS. I show below how it might work in your problem.

the DHMS function is a datastep function. It needs to be within a data or proc step, not in open code as you have it.

so your datastep code should look something like this:

hos_date = dhms("&curdate"d,0,0,0):

    if PCK_DELV_HRS = . then PCK_DELV_HRS = 0.00;
    if SPOT_HRS = . then SPOT_HRS = 0.00;
    if LINEHAUL_HRS = . then LINEHAUL_HRS = 0.00;

hos_date = dhms("&curdate"d,0,0,0):

run ;

When using macro variable, as in most other languages which use macros, you have two-step process in parsing code.

step 1. Macro processor (or pre-compiler) reads submitted code and determines if it needs to make substitutions and then makes substitutions if needed, It then submits the statement to data step compiler or procedure processor.

Step 2. The data step compiler parses the code in two passes, compiles the code and executes.

In the modified code above, the macro processor

1. simply passes the first 17 lines (data ....line_haul_rs = 0.0; .) to the data step compiler

2. substitutes the value of curdate and passes hos_date = dhms("7/22/2012"d,0,0,0): to data setp compiler.

3. passes run statement to compiler which then compiles and runs.

Re: Date from SAS showing 1/1/1960

LarryWorley — Mon, 22 Jul 2013 15:21:43 GMT

I missed the use of colon that Reeza pointed out.

Re: Date from SAS showing 1/1/1960

Reeza — Mon, 22 Jul 2013 15:23:50 GMT

Actually, you don't even need the macro variable, just replace the hos_date in the SQL query with the date() call.

proc sql;

insert into safety.safety_hrs_svc

select dhms(date(),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;

And...

Anything is easy when you know how and everything's hard when you don't know how.

Re: Date from SAS showing 1/1/1960

Fleroo — Mon, 22 Jul 2013 16:42:53 GMT

BINGO... REEZA. This works........select dhms("&curdate"d,0,0,0) as hos_date,

Larry, I indeed did click on the links you sent. Why do you assume I didn't ? Being so fresh, much of this is foreign to me, and it didn't make much sense. I'm not sure how deep I need delve into the world of SAS, as I inhereited a process to look after temporarily and it's in SAS. SAS is not my primary language I code in, and I doubt I'll need it a whole bunch going forward. THAT SAID. I appreciate you all taking the time to help me out, and I do see some neat features and benefits of using this as a primary language.

Re: Date from SAS showing 1/1/1960

LarryWorley — Mon, 22 Jul 2013 17:18:11 GMT

Fleroo,

Mea Culpa. I was frustrated and vented in the wrong direction.

Re: Date from SAS showing 1/1/1960

jakarman — Mon, 22 Jul 2013 17:36:28 GMT

larry, Fleero

The frustration about something at first looking that simple as date/time is quite understable.

Having been involved (set up testing facilties) with milo-testing time/date shifts the incredible complexity of dates became fully visable.
And even that you have not seen a glimse of discrepandancies of date/time indications used in the world. It is part of NLS support (National Language) and all SAS/Access parts (including Excel). Just recently some standards have been set: ISO8601 an those are not widley published/known.
You will be able to find references to those at SAS documentation when needed now. But they are not SAS specific, in contrary it is usable everywhere.

Re: Date from SAS showing 1/1/1960

Anotherdream — Mon, 22 Jul 2013 17:43:21 GMT

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

Re: Date from SAS showing 1/1/1960

Tom — Tue, 23 Jul 2013 05:39:42 GMT

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) ... ).