topic Re: Converting Date9. numeric variable to mmddyyyy date variable in SAS Programming

Converting Date9. numeric variable to mmddyyyy date variable

RACHEL2425 — Tue, 11 Jul 2023 14:12:30 GMT

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

Re: Converting Date9. numeric variable to mmddyyyy date variable

A_Kh — Tue, 11 Jul 2023 14:46:10 GMT

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;

Re: Converting Date9. numeric variable to mmddyyyy date variable

Reeza — Tue, 11 Jul 2023 14:59:19 GMT

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/ta-p/424354

Re: Converting Date9. numeric variable to mmddyyyy date variable

Tom — Tue, 11 Jul 2023 15:40:10 GMT

@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.

Re: Converting Date9. numeric variable to mmddyyyy date variable

RACHEL2425 — Tue, 11 Jul 2023 16:14:40 GMT

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

Re: Converting Date9. numeric variable to mmddyyyy date variable

Reeza — Tue, 11 Jul 2023 16:29:38 GMT

date_character = put(date_var, mmddyy8.);

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

Re: Converting Date9. numeric variable to mmddyyyy date variable

Tom — Tue, 11 Jul 2023 16:31:14 GMT

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;

Re: Converting Date9. numeric variable to mmddyyyy date variable

RACHEL2425 — Tue, 11 Jul 2023 16:49:04 GMT

I unfortunately have multiple years.

Re: Converting Date9. numeric variable to mmddyyyy date variable

RACHEL2425 — Tue, 11 Jul 2023 16:57:44 GMT

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

Re: Converting Date9. numeric variable to mmddyyyy date variable

Reeza — Tue, 11 Jul 2023 16:59:07 GMT

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.

Re: Converting Date9. numeric variable to mmddyyyy date variable

RACHEL2425 — Tue, 11 Jul 2023 17:07:12 GMT

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

Re: Converting Date9. numeric variable to mmddyyyy date variable

PaigeMiller — Tue, 11 Jul 2023 18:18:51 GMT

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;

Re: Converting Date9. numeric variable to mmddyyyy date variable

Tom — Tue, 11 Jul 2023 18:09:02 GMT

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