Sort by two variables one of which a date

[NewUsrStat]

Hi guys, 

how can I sort by ascending both IDs (replicated) and Starting_dates? 

ID           Starting_dates          Ending_dates

001           13JAN2021              08JUN2022

001           02FEB2019              09MAY2020

003           01JAN1998               07JUL2000

......          .................                 ...................

Desired output:

ID           Starting_dates          Ending_dates

003           01JAN1998               07JUL2000

001           02FEB2019              09MAY2020

001           13JAN2021              08JUN2022

......           .................                 ...................

I tried (without success): 

proc sort
data = have
out = want;
by id starting_dates
run;

thank you in advance

[svh]

you are missing the semicolon at the end of the line with the BY statement.

[PaigeMiller]

---

@NewUsrStat wrote:

Hi guys, 

how can I sort by ascending both IDs (replicated) and Starting_dates? 

ID           Starting_dates          Ending_dates

001           13JAN2021              08JUN2022

001           02FEB2019              09MAY2020

003           01JAN1998               07JUL2000

......          .................                 ...................

Desired output:

ID           Starting_dates          Ending_dates

003           01JAN1998               07JUL2000

001           02FEB2019              09MAY2020

001           13JAN2021              08JUN2022

......           .................                 ...................

I tried (without success): 

proc sort
data = have
out = want;
by id starting_dates
run;

---

For your future benefit, which should also be something that you address in this thread ... saying "without success" without providing information about what was unsuccessful is never helpful. We need to know what you see that indicates "without success". If there are errors in the log, show us the entire log (every single line, every single character). If the output is not what you expect, show us the output and explain what you expected.

Regarding problems with sorting dates, the dates must be valid numeric SAS date values. If sorting doesn't give you the desired output, perhaps you are using character dates, which are sorted alphabetically, and so 01APR2021 comes before 01JAN2021. On the other hand, if these are valid numeric SAS date values, they will sort numerically as you would expect. We don't know if your dates are numeric or character, because the information you provided doesn't make that clear. Please show us PROC CONTENTS for this data set so we can see if they are numeric or character.

To prevent this problem of lack of clarity about the data, we request that in the future, ALWAYS (100% of the time, no exceptions) provide data as working SAS data step code, which you can type in yourself (please test it first so that you can be sure the code is working) or you can create this SAS data step code following these instrucitons. 

[Astounding]

The program is fine (assuming you add the missing semicolon).  But the problem lies with the data.

Dates should not be stored as character strings.  They should be converted to numeric values on SAS's date scale.  What you did (improperly) was something like this:

input starting_dates $ 1-9;

What you should have done when reading the data was something like this instead:

input @1 starting_dates date9.;
format starting_dates date9.;

If you don't want to return to the original data set and re-read it, there are ways to convert what you have now to the proper form.

[Tom · Accepted solution]

If you want the DATE to be the primary sort order then tell SAS that in the BY statement.

data have;
  input ID $ (Starting_dates Ending_dates) (:date.);
  format Starting_dates Ending_dates date9.;
cards;
001 13JAN2021 08JUN2022
001 02FEB2019 09MAY2020
003 01JAN1998 07JUL2000
;

proc sort;
 by Starting_dates id;
run;

Result:

              Starting_      Ending_
Obs    ID       dates          dates

 1     003    01JAN1998    07JUL2000
 2     001    02FEB2019    09MAY2020
 3     001    13JAN2021    08JUN2022

[NewUsrStat]

No, I would like to sort dates by in ascending but splitting by IDs, i.e., for each ID.

[Tom]

---

@NewUsrStat wrote:
No, I would like to sort dates by in ascending but splitting by IDs, i.e., for each ID.

---

If you sort by ID and then DATE then you cannot get the output you requested.

Did you want to sort the IDs in descending order and the DATE in ascending order?

by descending ID Starting_dates ;

If not then you need to explain what that you actually want in more detail. Please provide example data where just sorting by date and id does not get what you want so it is more obvious what you do want.

[NewUsrStat]

So, I need to sort in ascending order the IDs and for each ID ascending order of dates

[Tom]

---

@NewUsrStat wrote:
So, I need to sort in ascending order the IDs and for each ID ascending order of dates

---

That is the default behavior when sorting.   

proc sort;
 by id starting_dates;
run;

But it will produce a different output than you requested.

If you want ID '003' to be sorted to before ID '001' for some reason other than that '003' is LARGER than '001' you need explain what is the reason for placing '003' before '001'.

              Starting_      Ending_
Obs    ID       dates          dates

 1     001    02FEB2019    09MAY2020
 2     001    13JAN2021    08JUN2022
 3     003    01JAN1998    07JUL2000