Date from SAS showing 1/1/1960

[Fleroo]

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

[Reeza]

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

[LarryWorley]

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.

[Fleroo]

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

[Anotherdream]

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

[Fleroo]

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 ?

[esjackso]

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.

EJ

[Fleroo]

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;  

[Fleroo]

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:

[LarryWorley]

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

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;

    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.

[LarryWorley]

I missed the use of colon that Reeza pointed out.

[Reeza]

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.

[Fleroo]

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.

[LarryWorley]

Fleroo,

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

[jakarman]

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.