Am trying to import a data set into SAS, but my date-time gives me errors, the date_time looks like this 20191202T071503+000, any help? below is the codes am using to import the dataset, kindly check the date_time portion if it looks ok.
data WORK.SCL1;
infile 'P:\DATA\SCL_1.csv\' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat RIC $12. ;
* TIP - Regardless of what SAS suggests as the length of the RIC variable increase it to above the
maximum length RIC in your dataset, which for us is 12 characters;
informat Date_Time datetime18.;
informat Type $5. ;
informat Price best32. ;
informat Volume best32. ;
informat Bid best32. ;
informat Ask best32. ;
format RIC $12. ;
format Date_Time datetime18.;
format Price best12. ;
format Volume best12. ;
format Bid best12. ;
format Ask best12. ;
input
RIC $
Date_Time
Type $
Price
Volume
Bid
Ask
;
run;
option obs=max;
I combined my earlier code into the data step, and added a condition to detect timestamps with a time zone.
I also added one of the offending lines from your log to the csv file, and it was read correctly.
data want;
infile "~/SCL.csv" dlm="," dsd truncover firstobs=2;
input
_ric :$6.
domain :$15.
_date_time :$35.
type :$6.
price
volume
bid_price
ask_price
;
if indexc(_date_time,"+")
then do;
if indexc(_date_time,":")
then do;
_date_time = cats(_date_time,":00");
date_time = input(_date_time,e8601dz35.);
end;
else do;
_date_time = cats(_date_time,"00");
date_time = input(_date_time,b8601dz20.);
end;
end;
else date_time = input(_date_time,e8601dt26.);
date = datepart(date_time);
time = timepart(date_time);
format
date_time e8601dz35.6
date yymmdd10.
time time15.6
;
*drop _date_time;
run;
As soon as you're satisfied, uncomment the DROP statement.
If there are ERRORs in the log, show us the log for this data step.
If the results are not what you expect, show us the results you are getting and state clearly what you expect to get.
For your future benefit, do state there are errors without further explanation, and without showing us the information requested above.
I asked to see the log, not two lines of the log.
Please show the entire log for this DATA step, starting with the DATA command, and continuing down to the last NOTE: after the DATA step.
Please format the log proprely, by copying it as text and pasting it into the window that appears when you click on the </> icon.
What you have is an ISO-compliant timestamp in basic notation, so you should use the B8601DZ informat with proper length.
By "import" do you mean read? Your posted code looks like the way that PROC IMPORT writes the data steps it generates, but I doubt that PROC IMPORT would decide to use the DATETIME informat to read strings that look like either of these
20191202T071503+000 2020-03-31T08:00:12.274400869+01
Both of those will cause trouble for SAS as neither is considered valid the the corresponding B8601DZ or E8601DZ informat.
The first needs a four digit time offset instead of 3. The second needs a colon in the time offset.
20191202T071503+0000 2020-03-31T08:00:12.274400869+01:00
You might want to read the value into a character string and then parse it into date, time and offset values using T and +/- as delimiters which you can then convert to DATE, TIME and TIME values independently.
From Informat documentation:(emphasis added)
Use + for time zones east of the zero meridian, and use – for time zones west of the zero meridian. For example, +0200 indicates a two-hour time difference to the east of the zero meridian, and –0600 indicates a six–hour time difference to the west of the zero meridian.Restriction: The shorter form +|–hh is not supported.
please how do I read the value into a character string and then parse it into date, time?
Here's an example how both of your questionable strings can be read successfully:
data want;
infile datalines dlm="," dsd truncover;
input _date_time :$40.;
if indexc(_date_time,":")
then do;
_date_time = cats(_date_time,":00");
date_time = input(_date_time,e8601dz35.);
end;
else do;
_date_time = cats(_date_time,"00");
date_time = input(_date_time,b8601dz20.);
end;
format date_time e8601dz35.6;
drop _date_time;
datalines;
20191202T071503+000
2020-03-31T08:00:12.274400869+01
;
You can also see how all times are converted to UTC.
Thanks Kurt for the reply, the codes work perfectly. But I have the date-time column to be part of other variables and I am trying to read them together. Please, I have attached a sample of csv file if you could kindly look at it and help me read them correctly. My ultimate goal is to split the date and time into separate columns in the data set.
Thanks
I copied your file as is to my ODA, and ran this code successfully:
data want;
infile "~/SCL.csv" dlm="," dsd truncover firstobs=2;
input
_ric :$6.
domain :$15.
date_time :e8601dz29.
type :$6.
price
volume
bid_price
ask_price
;
date = datepart(date_time);
time = timepart(date_time);
format
date_time e8601dt26.6
date yymmdd10.
time time15.6
;
run;
Thanks a lot for the reply, but after reading the data, most of the data points are missing. Kindly see the log attached.
I combined my earlier code into the data step, and added a condition to detect timestamps with a time zone.
I also added one of the offending lines from your log to the csv file, and it was read correctly.
data want;
infile "~/SCL.csv" dlm="," dsd truncover firstobs=2;
input
_ric :$6.
domain :$15.
_date_time :$35.
type :$6.
price
volume
bid_price
ask_price
;
if indexc(_date_time,"+")
then do;
if indexc(_date_time,":")
then do;
_date_time = cats(_date_time,":00");
date_time = input(_date_time,e8601dz35.);
end;
else do;
_date_time = cats(_date_time,"00");
date_time = input(_date_time,b8601dz20.);
end;
end;
else date_time = input(_date_time,e8601dt26.);
date = datepart(date_time);
time = timepart(date_time);
format
date_time e8601dz35.6
date yymmdd10.
time time15.6
;
*drop _date_time;
run;
As soon as you're satisfied, uncomment the DROP statement.
Thanks alot, really appreciate, it worked!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Ready to level-up your skills? Choose your own adventure.