DATA Step, Macro, Functions and more

Date/Time from ACCESS to Date/Time SAS

Reply
Contributor
Posts: 35

Date/Time from ACCESS to Date/Time SAS

Trying to understand why when I change the input to Date9,  the output is still as a date and time?

 

In the Data Import step I have changed it so that is only shows date no time, but that is not working any suggestions?

Trusted Advisor
Posts: 1,554

Re: Date/Time from ACCESS to Date/Time SAS

Posted in reply to TMiller16

If you have a numeric variable containing datetime you can convert it

into date variable and addapt its format:

 

data _null_;

   dt = dhms(today(),0,0,0);  /* a datetime variable */

  put dt datetime18.;

 

  dt = datepart(dt); /* covertint variable to date only */

  format dt date9;

  put dt=;

run;

  

   

 

 

Contributor
Posts: 35

Re: Date/Time from ACCESS to Date/Time SAS

ok, I understand that, now, SAS is not my primary so it's new to me.  I went though the wizards to set everything up.  In the Import Data step is where I tried to change to date9.  Where would I put the code for this to work?

Trusted Advisor
Posts: 1,554

Re: Date/Time from ACCESS to Date/Time SAS

Posted in reply to TMiller16

You have run probably code like:

    proc import .... out=have; ...run;

 

now add a dada step:

data want;

  set have;

       date = datepart(date);

      format date date9.;

run;

Super User
Posts: 11,343

Re: Date/Time from ACCESS to Date/Time SAS

Posted in reply to TMiller16

You may be missing some important information like the code used to read an external file. Their are only two types of data in SAS Numeric and character. How SAS displays specific values is controlled by the format. If you change the "input" you may not have changed the associated display format and the result is that the new values appear to be datetime but are actually dates displayed incorrectly.

Contributor
Posts: 35

Re: Date/Time from ACCESS to Date/Time SAS

Agreed, I did try and change it in the Field Attributes

Type: Date/Time
Source Date9
Output Date9
Output Informat Date9

but still showing as date and time
Super User
Posts: 11,343

Re: Date/Time from ACCESS to Date/Time SAS

Posted in reply to TMiller16

Time for some details about SAS and dates, times and datetimes. SAS date vales are the number of days since 1, Jan 1960, Datetime values are the number of seconds since midnight 1 Jan 1960. Changing the format after the value is assigned only changes appearance. if you want to get the date value from a datetime value then you use the SAS function DATEPART. If you do not change the value then regardless of what you do with the format the value remains the same and usually not as desired.

 

OR you change how the data is initially read with the correct informat and format assigned.

Maybe this little program will help a little:

data example;
   x= '10Oct2016 12:27:18'dt;
   put "as datetime" x= datetime20.;
   put "as general numeric" x= best16.;
   put "as date" x= date9.;
   y = datepart(x);
   put "as date9" y= date9.;
   put "as mmyydd10" y= mmddyy10.;
   put "as datetime" y= datetime20.;
   z=0;
   put "as datetime" z= datetime20.;
   put "as date" z = date9.;
run;

Note that attempting to display a datetime value not terribly far from Jan 1960 means that the seconds interpretted as days will overload the display format and yeild ******** as it exceeds the maximum year the format will display, 9999.

 

Ask a Question
Discussion stats
  • 6 replies
  • 305 views
  • 0 likes
  • 3 in conversation