02-12-2014 08:32 AM
I've got a Powershell script that reads an external database and spits the data out as a pipe delimited file. I then use a data step (basically a capture of PROC IMPORT generated code) to read the data into SAS.
The problem is, the Powershell script (part of an ETL job) runs under different user accounts, with different date format (locale) settings. Under my user account, with region settings date format of DD-MMM-YY and time format of HH:MMS (24 hour time), everything works ok. Under the batch scheduler account, with region settings date format of DD/MM/YY and 12 hour time format, the import fails.
Here is some sample code illustrating this issue:
options datestyle=locale; * default value ;
*options datestyle=dmy; * value appropriate to australia ;
*options datestyle=mdy; * just testing ;
*options datestyle=ymd; * just testing ;
infile datalines dlm="|" dsd;
attrib datetime length=8 informat=anydtdtm40. format=datetime.;
05/09/2013 03:09:11 PM
30/01/2013 03:09:11 PM
The 1st line is from my account, the 2nd & 3rd lines are from the batch scheduler account, the 2nd is ambiguous, the 3rd is unambiguous (and an "actual value' from the database). The other lines are hand edited testing values.
Submit the code with each setting of the datestyle option. Some output is unexpected (i.e. datestyle=ymd), but my main problem is that the 3rd line is always missing.
Here is an excerpt from the doc for anydtdtm:
"IF AM | PM is not present and the month and day values are ambiguous, the value for the DATESTYLE= system option is used to determine the order."
1) Why should the presence or absence of AM | PM have any difference on the ***DATE*** value as determined by the DATESTYLE option? I can see AM | PM affecting the time value if 24 hour time is not used, but I don't understand why it should affect the date derivation? Perhaps there's a good design reason, but I'm not seeing it. Any birdies want to chime in?
2) Is there any way I can read in line 3 as a datetime value (without fiddling with the pipe delimited file values)?
3) Alternatively, if anyone knows a way to configure Powershell to temporarily override the locale setting for the duration of the script, please let me know. Remember: I'm just outputting a recordset, not using any Get-Date formatting strings.