BookmarkSubscribeRSS Feed
RACHEL2425
Fluorite | Level 6

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 

12 REPLIES 12
A_Kh
Lapis Lazuli | Level 10

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
Super User

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
Fluorite | Level 6

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

PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
Tom
Super User Tom
Super User

@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
Fluorite | Level 6

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

Reeza
Super User
date_character = put(date_var, mmddyy8.);

However, this will definitely not sort if you have multiple years.
RACHEL2425
Fluorite | Level 6

I unfortunately have multiple years. 

Reeza
Super User
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
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

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


SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1680 views
  • 0 likes
  • 5 in conversation