topic Re: SAS importing date incorrectly in SAS Enterprise Guide

SAS importing date incorrectly

rfurtuna — Sat, 27 Nov 2021 04:07:54 GMT

Hello,

I'm trying to import a CSV file which has a column with date/time values as such: 01-10-2019 00:00 (as in October 1st, 2019, 12 AM). However, every time I try to import the data "01-10-2019 00:00" turns to "10JAN19:00:00:00" instead of "01OCT19:00:00:00". I've tried changing the date format in the import wizard, and it either does nothing or ends up messing up the data (replacing date values with "."). What's confusing to me is that the suggested/automatically chosen date format gives the example of "10MAR99:12:34:56" which is what I need, but then still imports it incorrectly. Any ideas how to fix this?

Data attached. Am on version 7.15 HF9.

Many thanks

Re: SAS importing date incorrectly

Tom — Sat, 27 Nov 2021 05:26:39 GMT

Why do you think 01-10-2019 means First of October instead of January Tenth?

What is your current setting for the DATESTYLE option?

If it is set to LOCALE then what is the current setting of the LOCALE option?

Re: SAS importing date incorrectly

Kurt_Bremser — Sat, 27 Nov 2021 08:49:46 GMT

Read into a temporary character variable, then force correct informats for the parts of the string:

data weather;
infile
  "/home/kurt.bremser/Tutorial1 - Hourly Weather Data.csv"
  dlm=","
  dsd
  truncover
  firstobs=2
;
input _datetime_local :$16.;
datetime_local = dhms(
  input(scan(_datetime_local,1," "),ddmmyy10.),
  0,0,
  input(scan(_datetime_local,2," "),time5.)
);
format datetime_local e8601dt19.;
drop _datetime_local;
run;

Re: SAS importing date incorrectly

rfurtuna — Sat, 27 Nov 2021 21:24:03 GMT

I ran:

options datestyle=dmy;
run;

And it fixed the issue.

Why do you think 01-10-2019 means First of October instead of January Tenth?

Thats the way it was interpreted in the tutorial. It also makes more sense to me for the time variable to be 24 hours per consecutive day in October, as opposed to 24 hours of January 10th, then 24 hours of Febuary 10th, and so on.

Cheers!