topic Re: How to sort by date in SAS Enterprise Guide

How to sort by date

deleted_user — Tue, 18 Sep 2007 20:19:21 GMT

How does one sort records by date? I've run my data through a Proc SQL step, and output the date fields in MMDDYY10. format - can this format then be sorted by date? If not, what should I be doing instead?

Thank you in advance....looking forward to your replies.

Re: How to sort by date

Doc_Duke — Wed, 19 Sep 2007 12:09:19 GMT

Hmm. I'm not really sure of the question. The Filter and Query task has a tab for sorting that is pretty obvious, so I suspect that is not what you are asking. Do you want the sorted output to look like

09/17/2006
09/17/2007
09/18/2005
09/18/2006
...
?

If so, the only way I know to do that is to create a new variable that is the text representation of the date. You can do that with the PUT function,
PUT(orig_date_var,mmddyy10.) in the expression builder.

HTH
Doc Muhlbaier

Re: How to sort by date

deleted_user — Wed, 19 Sep 2007 15:50:06 GMT

Hi, Doc, and thanks for replying. What I've done, in a Proc SQL step, is to use the DATEPART function of a field that contains a SAS datea and Time value. Once I got the DATEPART value, in a PROC SORT step, I then sorted the SAS numeric date value. After that, I then had a PROC REPORT STEP, and that's where my dates go crazy. I had PROC PRINT commands after both the PROC SQL and PROC SORT, and the dates looked great. However, nothing in my report showed as being in a valid sequence, whether I used native SAS date format or an edited date format of MMDDYY10..

That's the part that's driving me nuts. Also, how might I go about putting the date into a text format to look like the format you suggested?

Thanks for responding and I look forward to hearing back from you.

Re: How to sort by date

Cynthia_sas — Wed, 19 Sep 2007 22:02:01 GMT

I wonder if your dates are going crazy because you are letting PROC REPORT think they're numeric vars that need to be grouped. For example, if you had this:
[pre]
define jobcode / group;
define hiredate / 'Hire Date';
define salary / 'Salary';
[/pre]
then the default "usage" for both hiredate and salary would be ANALYSIS, with the SUM statistic as the default.

You might want:
[pre]
define jobcode / group;
define hiredate / order order=internal 'Hire Date';
define salary / 'Salary';
[/pre]

or play around with the other possibilities for order (Internal, Formatted, Data...) which you can find in the documentation.

cynthia

Re: How to sort by date

deleted_user — Thu, 20 Sep 2007 14:31:19 GMT

Hi, Cynthia! Would that also happen if I used ORDER to try to arrange the dates in the PROC REPORT segment? I am going to play around with what you wrote...sounds interesting.

You folks (you in particular) are wonderful in the help you provide.... thank you again.

Re: How to sort by date

deleted_user — Thu, 20 Sep 2007 14:32:46 GMT

Cynthia,

Duhhhh! Please disregard my last question - my brain is definitely not awake this morning! I saw you already had ORDER in your initial reply. Sorry.....

Re: How to sort by date

deleted_user — Thu, 20 Sep 2007 16:18:55 GMT

Hi, Cynthia,

Your suggestion worked like a charm. THANK YOU!!