help with data formats for date/time

[deleted_user]

I have inherited some tables that have job run dates and times stored as a single time, either column - 01jan08:00:00:00
I need to split this into a date and a time, either in a derived table, or as part of the reporting process.
Any suggestions?
Thanks
Clark

PS - we're using this data as the basis for BI portal reporting by users.

[darrylovia]

Clark
SAS has 2 functions that should help you

datepart() returns the date of a datetime value
timepart() returns the time of a datetime value

-D

[Cynthia_sas]

Hi:
In addition to the function mentioned, remember that many data sources used in the BI Platform must have formats associated with the data in the metadata definition for the table. So, it might be possible to do your reporting using functions in the report code; however, for many places in the Platform, if you want your users to access data sources and use the data source without manipulation on their part, you may with to investigate the various FORMATS available to you. Also, you may need to verify that the correct formats are associated with your data sources and information maps.

The code below shows the difference between just using a SAS format to display a piece of the date/time variable value and using a function to extract the date only and/or the time only. Remember that you can define new data items in the information map to make it easier for your end users to access and report on the data -- in which case, the specs for the date only or the time only column would move to your information map.

The downside of keeping the variable in date/time format is that any WHERE clauses you build to select data rows will have to be built to select entire date/time values (instead of just a DATE value). But if you make a new data item for DATE only or TIME only, then your WHERE clauses would be much more simplified.

If you need more help figuring out how to make this work with the BI Portal, your best bet would be to contact SAS Technical Support.
cynthia

[pre]
data mydates;
infile datalines;
input mydate : datetime.;
alt1 = mydate;
alt2 = mydate;
return;
datalines;
13sep2001:02:35:43
15nov1950:07:30:10
01jan08:00:00:00
23aug1951:22:45:15
run;

ods html file='c:\temp\datetime.html' style=sasweb;
proc report data=mydates nowd;
title 'DateTime Formats and Functions';
column ('Formats Only' mydate alt1 alt2) ('Computed Using Functions' dateonly timeonly);
define mydate / display f=datetime16. 'DateTime16';
define alt1 / display f=datetime20. 'DateTime20';
define alt2 /display f=datetime9. 'DateTime9';
define dateonly / computed 'Date Piece' f=mmddyy10.;
define timeonly / computed f=hhmm5. 'Time Only';
compute dateonly;
dateonly = datepart(mydate);
endcomp;
compute timeonly;
timeonly = timepart(mydate);
endcomp;
run;
ods html close;

[/pre]

[deleted_user]

it is a reality that people want date and time separate, but I find that expectation disappointing
When time information is also relevant, I find it no problem to extend a date constant (in a macro variable) for use as a datetime constant. For example, select all transactions since noon on &sysdate
where tran_date >= "&sysdate:12:0"dt ;

When seeking to report, there are many formats with names beginning DT. These were designed to provide just the date information from a datetime value.

Are there any formats designed to reveal only the time part? I expect so. If I cannot find the one I need, it is easy to construct a new one using proc format's picture statement and the date and time directives.

When separating time from the appropriate date, storage is needed for another numeric variable, adding a few gigabytes or much more for some transactional information systems like flight bookings and card transactions and phone call connections and internet activity.

Ok, sometimes I might want just the day -e.g. for sql-type joins, or just the time -e.g. for time-of-day analysis. Then I would extract the relevant part only when really needed through a view .... minimising use of storage and I/O, and runtime........

but people want it, and the customer is always right,,,,,, sigh


PeterC