topic Re: Converting non-homogenous dates to SAS dates in SAS Procedures

Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 19:36:35 GMT

Hello,

I am trying to convert a variable that contains multiple format (LASTPAY) to a single/ standardized format (NEWEXPDATE).

Any idea how one would go about that?

Thanks!

Re: Converting non-homogenous dates to SAS dates

collinelliot — Fri, 25 Aug 2017 15:41:20 GMT

How many variations of the date do you have? I see just two in your example. But you can conditionally convert using the approprirate format based on a pattern match. That is, if it looks like it's already a SAS date value, just input(lastpay, best.). If it's a date format, input(lastpay, mmddyy10.), for example.

Re: Converting non-homogenous dates to SAS dates

Jagadishkatam — Fri, 25 Aug 2017 15:50:51 GMT

data have;
input lastpay$10.;
newexpdate=input(lastpay,mmddyy10.);
format newexpdate mmddyy10.;
cards;
42917
6/27/2017
 ;
run;

Re: Converting non-homogenous dates to SAS dates

Reeza — Fri, 25 Aug 2017 15:56:18 GMT

Are the values you've shown correct, ie the is the correct new variable in that data set?

Can you provide data as text to test?

Re: Converting non-homogenous dates to SAS dates

ballardw — Fri, 25 Aug 2017 16:42:10 GMT

Most of your "dates" look like the Excel days from their offset date of 1 Jan 1900 except for a few that were entered as text in a manner that Excel did not convert to its internal date.

One way: Make sure that the entire column in the Excel sheet has the cell format set to a date.

Then export to CSV and import into SAS.

Re: Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 19:35:53 GMT

Hello!

There is four (the data wasn't assembled by me and it the process of aggregating the dataset, the conversion wasn't done at the source unfortunately):

42916

6/23/2017

20170630

2017-06-21

I've attached a sample.

Let me know!

Thank!

Re: Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 19:37:50 GMT

I've attached a sample, there are at least 4 different formats (that I could find).

Thanks for the help!

Re: Converting non-homogenous dates to SAS dates

art297 — Fri, 25 Aug 2017 19:50:58 GMT

Here is one way:

data have;
  input havedate $10.;
  cards;
42916
6/23/2017
20170630
2017-06-21
;

data want;
  set have;
  format wantdate date9.;
  wantdate=input(havedate,anydtdte10.);
  if missing(wantdate) then wantdate=input(havedate,8.)-21916;
run;

Art, CEO, AnalystFinder.com

Re: Converting non-homogenous dates to SAS dates

ballardw — Fri, 25 Aug 2017 20:12:15 GMT

There is another complication in your example data:

You are showing a Lastpay value of 42909 that, depending on the row, apparently is supposed to transform to 09/01/2020, 11/22/2019, 05/01/2021. Is that actually the case?

Against my better judgement I opened that Excel file.

I copied the data column to another column to manipulate in Excel. Highlight the new column, go to the data tab, select the Text to columns and convert everything to numeric. Then applied the DATE format to the cells.

This is what i get for the first few rows (tab delimited text with a blank column to help readability)

LASTPAY		After Text to column and apply date format.
42887		6/1/2017
6/24/2017		6/24/2017
42887		6/1/2017
42917		7/1/2017
42906		6/20/2017
2017-06-30		6/30/2017
42911		6/25/2017
6/30/2017		6/30/2017
42917		7/1/2017
42916		6/30/2017
42907		6/21/2017
42887		6/1/2017
42916		6/30/2017
42901		6/15/2017
42905		6/19/2017
42908		6/22/2017
42916		6/30/2017
42917		7/1/2017
42922		7/6/2017
42907		6/21/2017
42923		7/7/2017
42915		6/29/2017

I suspect that your process somewhere along the line turned most of your dates into character values after removing the date format for some reason.

Re: Converting non-homogenous dates to SAS dates

art297 — Fri, 25 Aug 2017 20:23:13 GMT

@ballardw: You have more courage than I do. I attempted to open the file using Chrome's fairly safe viewer, but it was unable to open the file so I decided not to open it in Excel.

Art, CEO, AnalystFinder.com

Re: Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 20:31:17 GMT

Ya I think that's the problem - unfortunately, all I have is a serveral million observations with that glitch in it and I can't really go back to the source and ask them to fix the raw variables (the dataset is an amalgation of multiple sources, all with different formats) - so I was hoping that I could somehow just fix it within SAS.

Re: Converting non-homogenous dates to SAS dates

Tom — Fri, 25 Aug 2017 21:26:30 GMT

This question has come up before. Basically you have a column in Excel that has mixed dates and character strings. So when you convert it into a SAS dataset using PROC IMPORT or LIBNAME engine SAS will set the variable type to character. The dates come over as the text version of the integer value that EXCEL uses for dates. You just need to convert them. I would test if the value is an integer and then convert the integer by correcting for the difference in offset dates. And then use the ANYDTDTE informat to convert the other values.

data want ;
  set have ;
  date = input(chardate,??11.);
  if missing(date) then date=input(chardate,anydtdte11.);
  else date=date + '01JAN1900'd -2 ;
  format date date9.;
run;

The -2 in the conversion formula is because SAS starts counting from zero and Excel starts counting from one and Excel mistakenly thinks that 1900 was a leap year.

Note you could still have trouble if your multiple sources come from locations where people use MDY ordering for dates and also from places where people use DMY ordering for dates. In that case there really is no way to tell which is the right date without some additional knowledge. For example if you know the dates should always be the first of the month then you would know the '1/7/2017' meant 01JUL2017 and not 07JAN2017. Or if you know the source location of the record you could use that. Otherwise there will be no way to convert a value like '10/12/2016' with 100% convidence.

Re: Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 21:19:38 GMT

This code I found much more accurate:

data work.want;

set work.have;

format datenew date9.;

datenew =input(datehave,8.)-21916;

if missing(datenew ) then datenew =input(datehave,anydtdte10.);

run;

Re: Converting non-homogenous dates to SAS dates

camfarrell25 — Fri, 25 Aug 2017 21:20:54 GMT

I found this one to be quite helpful:
data work.want;

set work.have;

format datenew date9.;

datenew =input(datehave,8.)-21916;

if missing(datenew ) then datenew =input(datehave,anydtdte10.);

run;

Re: Converting non-homogenous dates to SAS dates

art297 — Fri, 25 Aug 2017 21:33:55 GMT

I prefer the order the way I proposed as, otherwise, a date like 20170630 doesn't translate correctly and you end up with all kinds of notes in your log.

Art, CEO, AnalystFinder.com

Re: Converting non-homogenous dates to SAS dates

ballardw — Fri, 25 Aug 2017 22:19:04 GMT

If you only have to process this one time I would suggest the Open in Excel, highlight and change text to numeric approach.

If you have to do this frequently I would start documenting how much time this takes as eventually what ever approach you take that works with this file is likely to fail, hopefully no date at all rather than a date that causes more issues later, and will need additional time to identify and correct.

At some point you may have documented enough time=man hours=Co$t to the organization that the process at least gets reviewed.

It may be there is no formal data "agreement" for combining the multiple data sources as to what goes in which column, what format and what file type. A lack of this type is common cause of "weird" data issues such as you are experiencing. There may be other people upstream of you that are also having issues around combining this data.

I decided long ago that I would avoid using actual Excel files for interchange because of the file size limits. Once upon a time 64K rows was the limit and I routinely dealt with orders of magnitude above that. Having someone try to tell me to process 10 or 100 files after spitting the data apart just to use Excel (and why? they couldn't actually do anything in Excel without the whole data set) was a waste of everyones time.