Frustrating date format

[texasmfp]

I am downloading some data from a European data page (Eurostat).  There is a date field with an unusual format and I cannot get SAS to understand it and then, to set it to standard US date format.  Here is the data: "23DEC15:13:47:00".  SAS thinks this is December 23, 2015 (plus the time - DATETIME19.), but it should be December 15, 2023.  Over two hours of reading threads and testing several "solutions" and no luck.  Help 🙂

[PaigeMiller]

I think you are going to have to parse the text string yourself so that 15 is the day and 23 is the year. Then re-assemble into a valid SAS datetime value.

[Patrick]

Here one way:

data demo;
  have_str="23DEC15:13:47:00";
  want_dttm=input(prxchange('s/^(\d\d)(\w{3})(\d\d)/$3$2$1/',1,strip(have_str)),datetime.);
  format want_dttm datetime21.;
run;

[Tom]

If you prefix the string with the century then ANYDTDTM9. will get the right day, but ANYDTDTM18. will not.   So you could use ANYDTDTM9. to get the day as a DATETIME and then just add the time of day value.

data have;
  string="23DEC15:13:47:00" ;
  longstring='20'||string ;
  dt = input(longstring,anydtdtm9.)+input(string,anydttme18.);
  format dt datetime19.;
  put string= / dt=;
run;

2122  data have;
2123    string="23DEC15:13:47:00" ;
2124    longstring='20'||string ;
2125    *dt = input(longstring,anydtdtm18.);
2126    dt = input(longstring,anydtdtm9.)+input(string,anydttme18.);
2127    format dt datetime19.;
2128    put string= / dt=;
2129  run;

string=23DEC15:13:47:00
dt=15DEC2023:13:47:00

[texasmfp]

Thanks but neither of these works.  The data is not a string.  I apologize for placing the date in quotes.  This field, along with others is downloaded from Eurostat via PROC HTTP.  When SAS intakes the data from the web, it formats it as a date:

259 format LAST_UPDATE datetime. ;
MPRINT(RUNIMP.IMP): format LAST_UPDATE datetime. ;

It saves the data to a CSV file with what looks like a string, but when I bring in the CSV file, SAS thinks its a date.  When I force it to a number, it gives me 1766497620, which apparently is December 23, 2015.

[Patrick · Accepted solution]

---

@texasmfp wrote:

Thanks but neither of these works.  The data is not a string, its a number, a date in fact.  I apologize for placing the date in quotes.  This field, along with others is downloaded from Eurostat via PROC HTTP.  When SAS intakes the data from the web, it formats it as a date:

---

Ah! This makes much more sense because I was very surprised that Eurostat would format a datetime string in such an uncommon way. 

Your screenshot shows us that the source must be a string that you then read into a numerical SAS Datetime value using informat ANYDTDTM40 to which you apply a format of datetime16 for printing(display). 

The SAS anydtdtm.. informat converts the source string to a number that is the count of seconds since 1/1/1960. This is what's stored internally in the SAS numerical variable. Format datetime16 just instructs SAS how to print this number so it's human readable.

Anydt... informats accept various date/datetime/time patterns as source. Unfortunately the US MMDD.. and the rest of the world's DDMM... pattern are not mutually exclusive and though it depends which pattern the ANYDT... informat tries first. 

Given this is a European site I assume the source pattern to be DDMM... but that you're working in a SAS environment with a US local that first tries MMDD...

Set OPTION DATESTYLE=DMY prior to the step where you read the data into SAS.

If this still doesn't work and if the data you download is publicly available then please share your code - especially the Proc HTTP for download of the data - so we can try ourselves.

If the data is not public then use as informat and format $CHAR40. and then share with us how the string value from source looks like.

[texasmfp]

that was it.  the options statement combined with a subsequent datetime16 format when the program spits out the final database solved it.  Much thanks

[Patrick]

---

@texasmfp wrote:

that was it.  the options statement combined with a subsequent datetime16 format when the program spits out the final database solved it.  Much thanks

---

@texasmfp  Just for clarity: It's the INFORMAT together with the Datestyle option that makes SAS convert the source string into the right datetime number for SAS. The format has nothing to do with how the source string gets converted to a SAS datetime value.

The format just plays a role for display and it's something you could change later on without any issues/changing the internally stored datetime value.

It saves the data to a CSV file with what looks like a string

You could also open this .csv with a text editor (NOT Excel) and look at the actual string and then use the matching SAS informat for the pattern - one that only accepts either MMDD... or DDMM... This way the value of the datestyle option is of no relevance. 
If you use the datestyle option be sure to set it back to the initial value for your environment as else other parts of your code that run in the same session and convert MMDD... strings to dates might start to return undesired results if you're using an ANY... informat there as well.

[Tom]

You are still missing steps in explaining what you did.

PROC HTTP will NOT make a dataset.  At best it will get you a FILE.  

How did you try to convert the file into a dataset?

If you let PROC IMPORT make a GUESS at how to read it then that is probably the source of the confusion. 

Look at the file you got and figure out for yourself what style the datetime strings are in and then write a data step that can read that style properly.

If you cannot figure out how to look at the file yourself then run a data step and use the LIST statement to dump a few of the lines to the SAS log.

data _null_;
  infile 'file I got by running PROC HTTP' obs=10;
  input;
  list;
run;