Converting Date9. numeric variable to mmddyyyy date variable

[RACHEL2425]

Hi everyone, 

I am currently trying to sort a dataset based on date, and it wont sort correctly as the current date format is a numeric date9. format. 

How do i convert this variable to a date variable? Please note that I do not want to just change the format, I want to either create a new date variable based on the date9. variable or convert the variable altogether. 

Thank you, 

Rachel 

[A_Kh]

Hi, 

If your date variable type is numeric then it should sort correctly, regardless the format used. 

But if your variable type is character then you need to convert it into numeric, using INPUT() function and assigning the desired format (eg. DATE9. ); 

Example:

data want;
set have;
date1= input(date, mmddyy10.);
format date1 mmddyy10.;
run; 

[Reeza]

---

current date format is a numeric date9. format. 

---

Assuming the above is correct, then the method of changing it to MMDDYYYY is to change the format. 

format varname mmddyy10.;

If you change it to a character variable it will not sort/order correctly and cannot extract the day/time components as necessary. 

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

[RACHEL2425]

This issue with this is that it only changes the format, not the informat. 

[PaigeMiller]

Informat is not relevant here. To sort, you want numeric variables (not character variables), the format is also irrelevant, numeric values that have DATE9. format will still sort properly. Example:

data fake;
    date='01APR2019'd;
    output;
    date='01JAN2019'd;
    output;
    format date date9.;
run;
proc sort data=fake out=fake1;
    by date;
run;

[Tom]

---

@RACHEL2425 wrote:

Hi everyone, 

I am currently trying to sort a dataset based on date, and it wont sort correctly as the current date format is a numeric date9. format. 

How do i convert this variable to a date variable? Please note that I do not want to just change the format, I want to either create a new date variable based on the date9. variable or convert the variable altogether. 

Thank you, 

Rachel 

---

Something does not make sense about your description of the problem.

If you sort by a NUMERIC variable that has the DATE9. format attached to it then the values will sort by actual values (number of days) and not the displayed text (ddMONyyyy).

How are you sorting?

Also if you change the display format to something like the MMDDYYYY mention in the subject line (did you mean use the MMDDYYN8. format?) then it still will not sort properly.  Only if you put the date value into strings in YYYYMMDD style will the resulting string sort properly.

[RACHEL2425]

Hi, thank you so much. How would I put the date9. format into string style?

[Reeza]

date_character = put(date_var, mmddyy8.);

However, this will definitely not sort if you have multiple years.

[RACHEL2425]

I unfortunately have multiple years. 

[Reeza]

Then just applying the format as in my initial answer should allow your data to sort correctly. If it's not, please show your code, log and a screenshot of the sorted data that is incorrect so we can help you debug the issue.

[Tom]

To create a string using a format use the PUT() function.

data want;
  set have;
  date_string = put(date_number,date9.);
run;

To create a string using whatever format is attached to the existing variable use the VVALUE() function.

data want;
  set have;
  date_string = vvalue(date_number);
run;

[RACHEL2425]

Hi, again! thank you. it created the character string date. However, i have multiple years and the sort function does not appear to work.

[Tom]

For sorting you should keep the values as actual dates.

Let's make an example with a few date values and variables with different TYPES and different attached display formats.

data have;
  input date1 :date. ;
  date2 = date1 ;
  format date1 date9. date2 yymmdd10. ;
  string1 = put(date1,date9.);
  string2 = put(date1,mmddyy10.);
  string3 = put(date1,yymmdd10.);
cards;
01JAN2023
03OCT2022
10MAR2021
;

If we sort by either of the two actual date variables we get:

OBS        date1         date2     string1      string2       string3

 1     10MAR2021    2021-03-10    10MAR2021    03/10/2021    2021-03-10
 2     03OCT2022    2022-10-03    03OCT2022    10/03/2022    2022-10-03
 3     01JAN2023    2023-01-01    01JAN2023    01/01/2023    2023-01-01


If we sort by STRING2 (value in MDY order) then we get.  (Plus it is difficult to know which of those last two values represents MARCH and which represent OCTOBER since half of world used DMY order instead of MDY order)

OBS        date1         date2     string1      string2       string3

 1     01JAN2023    2023-01-01    01JAN2023    01/01/2023    2023-01-01
 2     10MAR2021    2021-03-10    10MAR2021    03/10/2021    2021-03-10
 3     03OCT2022    2022-10-03    03OCT2022    10/03/2022    2022-10-03

If we sort by STRING1 (value in DATE style) then we get

OBS        date1         date2     string1      string2       string3

 1     01JAN2023    2023-01-01    01JAN2023    01/01/2023    2023-01-01
 2     03OCT2022    2022-10-03    03OCT2022    10/03/2022    2022-10-03
 3     10MAR2021    2021-03-10    10MAR2021    03/10/2021    2021-03-10

But if we sort by  STRING3 (value in YMD order) then we get the same thing as sorting by the actual date value.

OBS        date1         date2     string1      string2       string3

 1     10MAR2021    2021-03-10    10MAR2021    03/10/2021    2021-03-10
 2     03OCT2022    2022-10-03    03OCT2022    10/03/2022    2022-10-03
 3     01JAN2023    2023-01-01    01JAN2023    01/01/2023    2023-01-01