topic Re: date_ time split in SAS Programming

date_ time split

otalog — Thu, 13 Jan 2022 04:06:22 GMT

My ultimate goal is to split the date and time into separate columns in the dataset attached below, please kindly help me out.

Re: date_ time split

ballardw — Thu, 13 Jan 2022 04:20:33 GMT

If you have an actual SAS datetime value (can't tell from a CSV as it is not a SAS dataset yet) the function DATEPART returns the date portion and the function TIMEPART returns the time portion such as:

data want;  
   set have;
   datevariable = datepart(datetimevariable);
   timevariable = timepart(datetimevariable);
   format datevariable date9. timevariable time8.;
run;

Pick appropriate date format, your choice.

Re: date_ time split

otalog — Thu, 13 Jan 2022 04:24:03 GMT

Thanks for the quick reply, but please how do I read the file correctly into sas before going ahead to split it?

Re: date_ time split

Kurt_Bremser — Thu, 13 Jan 2022 09:37:48 GMT

See here: https://communities.sas.com/t5/SAS-Software-for-Learning/DateTime-format-error/m-p/789919/highlight/true#M347

Re: date_ time split

Ksharp — Thu, 13 Jan 2022 12:14:29 GMT

filename x 'c:\temp\SCL.csv' encoding='utf8' termstr=crlf;
proc import datafile=x out=have dbms=csv replace;
guessingrows=max;
run;

data want;  
   set have;
   datevariable = datepart(Date_Time);
   timevariable = timepart(Date_Time);
   format datevariable date9. timevariable time8.;
run;

Re: date_ time split

Tom — Thu, 13 Jan 2022 13:08:00 GMT

Just read the Date-Time column as a character string.

filename csv temp;
options parmcards=csv;
parmcards;
#RIC,Domain,Date-Time,Type,Price,Volume,Bid Price,Ask Price
0DZC.L,Market Price,2019-12-02T07:51:08.550270041Z,Quote,,,21.64,
0DZC.L,Market Price,2019-12-02T07:51:08.551068323Z,Quote,,,21.64,22.74
0DZC.L,Market Price,2019-12-02T08:11:47.326609096Z,Quote,,,21.4,22.74
0DZC.L,Market Price,2019-12-02T08:11:47.327322633Z,Quote,,,21.4,22.48
;

data have;
  length ric $10 domain $20 datetime $32 date time 8 type $10 price volume bid ask 8;
  infile csv dsd firstobs=2 truncover ;
  format date yymmdd10. time time15.6 ;
  input ric domain datetime type price volume bid ask;
  date = input(datetime,yymmdd10.);
  time = input(scan(datetime,2,'TZ'),time20.);
run;

Results

Re: date_ time split

otalog — Mon, 17 Jan 2022 05:37:14 GMT

Thanks for your reply, but please, after using the code and running with my entire data, most of the time variable do not appear.

Re: date_ time split

otalog — Mon, 17 Jan 2022 06:35:09 GMT

Thank you very much for the reply, the code works very well, but the datetime of this 2019-12-02T07:55:01.120950718+01 has missing time variable after spliting into date column and time column. So I wish to maintain the datetime format in the first row same as the second row, before spliting it into date and time. please how do I do it.
#RIC Domain Date-Time Type Bid Price Ask Price
0E64.L Market Price 2019-12-02T07:55:01.120950718Z Quote 79
0E64.L Market Price 2019-12-02T07:55:01.120950718+01 Quote 79 83