Struggles in converting "MM/DD/YYYY" character string to SAS datetime.

[sirmurphalot]

Hi friends,

I have some data that I need to subset based on which dates given as "MM/DD/YYYY" are between a certain range of dates (say -- a 10 week period starting December 2015). I have tried quite a lot of things to convert my date variable into SAS datetimes so that I can then use a where statement to subset to what I want. I've read A LOT of existing conversations on problems like this, but to no avail. My current code looks like (weekdate is the variable name):

data testdate;
set perm.my_data_set(obs=50);
new_date = dhms(input(weekdate, "MMDDYY8."),,,0);
run;

....and then proc print, etc. From here I get the error:

ERROR 85-322: Expecting a format name.
ERROR 76-322: Syntax error, statement will be ignored.

Any help would be very appreciated. I've also done some stuff with input(), put(), and format(). Again -- errors galore.

Representative data of variable in question:

weekdate

10/5/2015

1/31/2015

5/24/2015

1/19/2016

7/20/2016

12/1/2015

[kiranv_]

Please show some representative/sample data, so that someone could help you. Otherwise it is very difficult to guess.

[sirmurphalot]

Certainly!  The head of the variable in question looks like:

weekdate

5/8/2015

9/31/2015

11/20/2015

1/29/2016

4/20/2016

11/8/2015

[Tom]

The INPUT() function does not want the format as a string, just specify the actual format directly.  Make sure to use a width that is long enough.

input(weekdate, MMDDYY10.)

If you do want to provide the format as a string , say via a character variable in your data, then you could use the INPUTN() function. There is also an INPUTC() function for generating character results.

[sirmurphalot]

With this edited line I get this new error:

new_date = dhms(input(weekdate, MMDDYY8.),,,0);

ERROR 159-185: Null parameters for DHMS are invalid.

 ----------------------------------------------------------------------

If I just use the line:

new_date = input(weekdate, MMDDYY8.);

things run, but I get the result of:

new_date

.

.

.

.

.

.

which isn't useful for eventually using a where statement to subset my data.

[Tom]

If it doesn't like nulls for hours and minutes then use zero instead.

[sirmurphalot]

Sorry, perhaps I should have mentioned that I am very new to SAS.  How would I go about doing that?

[Tom]

You can use the INPUT() function with the MMDDYY10 informat to convert your date strings into date values.

You can use the DHMS() function to convert a date value to a datetime value.

Don't forget to attach formats to the variables so that they print the values in a way that humans can understand them.

Here is an example program using some the example string values that you posted.

data test;
  input weekdate $20. ;
  new_date=input(weekdate,mmddyy10.);
  new_datetime = dhms(new_date,0,0,0);
  format new_date date9. new_datetime datetime20. ;
cards;
10/5/2015
1/31/2015
5/24/2015
1/19/2016
7/20/2016
12/1/2015
;

That will produce

Obs    weekdate      new_date            new_datetime

 1     10/5/2015    05OCT2015      05OCT2015:00:00:00
 2     1/31/2015    31JAN2015      31JAN2015:00:00:00
 3     5/24/2015    24MAY2015      24MAY2015:00:00:00
 4     1/19/2016    19JAN2016      19JAN2016:00:00:00
 5     7/20/2016    20JUL2016      20JUL2016:00:00:00
 6     12/1/2015    01DEC2015      01DEC2015:00:00:00

Do you know if you actually need datetime values instead of date values?

So your program should look like.

data testdate;
  set perm.my_data_set(obs=50);
  new_date = dhms(input(weekdate, MMDDYY10.),0,0,0);
  format new_date datetime20. ;
run;

[sirmurphalot]

I don't think I have any preference for datetime vs. date -- all I need is to eventually subset on this variable using a where statement.  I ran the end code you gave and I am again only getting "." values on the new variable.  

Quick question about your example -- to use the "cards" function must I explicitly copy down the values of the variable?  Is it possible to do:

cards;

weekdate

;

Thanks for all the help!

[Tom]

---

@sirmurphalot wrote:

Quick question about your example -- to use the "cards" function must I explicitly copy down the values of the variable?  Is it possible to do:

cards;

weekdate

;

Thanks for all the help!

---

In a word, no.  I posted the data step with the in-line data so that you could see a working example. If you already have a dataset and a variable then you want to use a SET statement (or MERGE, UPDATE, MODIFY, etc.) to bring in the values from your existing dataset instead of reading values from a text file (INFILE) or in-line stream (CARDS or as they now call it DATALINES).

If you are getting missing values then your source dataset is not like what you reported to us.

Check your input variable. Is it character? or is it a numeric variable that already has a format attached to it?

Are you getting notes in log about converting numbers to characters or vice versa?  Are you getting notes about uninitialized variables?

Post the actual text from your SAS log. Remember to use the Insert Code button (looks like {i}) on the tool bar to preserve the formatting.

[sirmurphalot]

I found a note from the log saying that my input variable was numeric, so I ran the following code:

13         
14         data testdate;
15         	set perm.my_data(obs=50);
16         	/* Find new code below */
17         	date2 = input(put(weekdate,10.), MMDDYY10.);
18         	format date2 MMDDYY10. ;
19         run;

NOTE: There were 50 observations read from the data set PERM.MY_DATA.
NOTE: The data set WORK.TESTDATE has 50 observations and 42 variables.

This worked -- but now I am confronted with a strange new problem.  When I output my data to take a look I am getting this:

weekdate	date2
10/10/2015	2/3/1971
10/31/2015	2/3/1992
11/28/2015	2/4/2020
1/23/2016	2/4/1976
2/20/2016	2/5/2004
12/19/2015	2/4/1941

Any idea why I am getting weird new dates for date2?

[Tom · Accepted solution]

Looks like you already have a date variable. What are you trying to create?

Your code took a date value.  Printed the raw number of days since 1960 as a number. Then interpreted that number as if was formatted in MMDDYY order.  

765  data test;
766    input (weekdate new_date) (:mmddyy10.);
767    put 'Input line ' _infile_;
768    put 'RAW        '  (weekdate new_date) (=) ;
769    put 'DATE9.     '  (weekdate new_date) (=date9.) ;
770  cards;

Input line 10/10/2015  2/3/1971
RAW        weekdate=20371 new_date=4051
DATE9.     weekdate=10OCT2015 new_date=03FEB1971

So the date '10OCT2015'd is day number 20,371 in SAS's method of storing dates.  So the PUT(weekday,10.) function generated the string '     20371'.  You then read that using MMDDYY10 informat. So the '71' was interpreted as the year, the '03' as the day of the month and '2' as month of the year. 2071 would be in the future so it used 1971 instead. So you got '03FEB1971'd.

[sirmurphalot]

Thanks for all your help -- I appreciate it.