turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Building dates out of strings

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 10:02 AM

Howdy folks, so i understand there are numerous ways to go about moving from string to date conversions but I'd like to here an expert's opinion on the most efficient and simplest way to go about this. I'll work with Excel analogies since that is where I came from before.

In Excel I'd get an annoying string with an embedded date. Cell A2 = FEB2014025IMANNOYING

I'd use Left/Mid functions to parse out the date portions of string

cell A3 = MID(A2,4,4) = 2014

cell A4 = MID(A2,8,2) = 02

cell A5 = MID(A2,10,1)=5

I'd concatenate strings to make it look convenient.

cell A6 = A4 &"/"& A5 &"/"& A3 = 02/5/2014

And then change it to date format

=DATEVALUE(A6) = 2/5/2014 (as a numeric date format).

It looks like the substring function is the equivilant of LEF/MID/RIGHT in Excel, and PUT/INPUT are used to move from string to numeric and vis-versa. Does the approach I've described above make sense to do within SAS or SQL?

For a SAS example, I have an order date string 14020559810D452 or some such, where the first six digits represents a date.

I SUBSTR the value to arrive at 140205, but am at a loss on how to get to a date formatted 2/5/14.

If you have recommended site I can just dig into that would also work.

TS

Accepted Solutions

Solution

02-12-2015
10:15 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 10:15 AM

You're actually asking a multi-part question. You may have to read a little more about each of these parts.

Part 1: How does SAS store dates?

SAS stores dates as integers, where you add 1 to move from one day to the next. Arbitrarily, January 1, 1960 is day "zero" on SAS's scale.

Part 2: How do you get SAS dates to "look like" a date?

Add a format. For example:

var = 0;

format var yymmdds10.; ==> 1960/01/01

format var date9.; ==> 01JAN1960

Part 3: How to convert from character strings to SAS dates?

I strongly suggest hard-coding the "20" as necessary, so you are dealing with 4-digit years. The details depend on the string coming in. Your "order date" string of 14020559810D452 could be converted using:

datevar = input("20" || order_string, yymmdd8.);

The INPUT function reads character strings and converts them. The instructions (yymmdd8.) say read the first 8 characters, expect them to be in year-month-day form, and convert whatever you find.

There are plenty of details on each of these topics, but those are the basics.

Good luck.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 10:13 AM

You could do it as above, though you can go directly to the input function.

The input function is worth a read, as well as the mdy() function.

Input(date_string, yymmdd6.);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 10:13 AM

Well, I would first go with:

attrib date_var date9.;

date_var=mdy(input(substr(your_string,3,2),best.),input(substr(your_string,1,2),best.),input(substr(your_string,5,2),best.));

So yes, substr() works like MID,LEFT etc. its substr(*string*,*start,num characters*)

mdy() function needs three numbers, one for month, one for day, one for year. As you are taking characters from the string we need to convert those to numeric, we do this with the input function, the format best. is a kind of catch all format, we could equally use 2.

The mdy() function then feeds a number (its a number of days since a certain date which is how SAS stores dates - and Excel if I remember rightly) into a variable which has a date format applied, so that number gets displayed like a date.

Solution

02-12-2015
10:15 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 10:15 AM

You're actually asking a multi-part question. You may have to read a little more about each of these parts.

Part 1: How does SAS store dates?

SAS stores dates as integers, where you add 1 to move from one day to the next. Arbitrarily, January 1, 1960 is day "zero" on SAS's scale.

Part 2: How do you get SAS dates to "look like" a date?

Add a format. For example:

var = 0;

format var yymmdds10.; ==> 1960/01/01

format var date9.; ==> 01JAN1960

Part 3: How to convert from character strings to SAS dates?

I strongly suggest hard-coding the "20" as necessary, so you are dealing with 4-digit years. The details depend on the string coming in. Your "order date" string of 14020559810D452 could be converted using:

datevar = input("20" || order_string, yymmdd8.);

The INPUT function reads character strings and converts them. The instructions (yymmdd8.) say read the first 8 characters, expect them to be in year-month-day form, and convert whatever you find.

There are plenty of details on each of these topics, but those are the basics.

Good luck.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-12-2015 04:20 PM

Benji?