topic Re: proc SQL order by a negative date column? in SAS Procedures

proc SQL order by a negative date column?

CharlieSong — Sun, 20 Mar 2016 03:44:15 GMT

Hi,

Can a order by followed by a negative date column?

I have a code copied from somewhere like

PROC SQL;
	CREATE TABLE mcapData AS
	SELECT a.permno,
	a.date,
	a.mcap
	FROM retData AS a
	ORDER BY a.permno,
	-a.date;
	QUIT;

Is that a negative sign or just some error?

Re: proc SQL order by a negative date column?

PGStats — Sun, 20 Mar 2016 03:50:25 GMT

It is just a trick which should be written

BY a,permno, a.date DESC;

Re: proc SQL order by a negative date column?

CharlieSong — Sun, 20 Mar 2016 03:52:17 GMT

Thanks! You mean I can write like that but better use DESC?

Re: proc SQL order by a negative date column?

PGStats — Sun, 20 Mar 2016 03:54:25 GMT

DESC states what you want more clearly. The next person looking at the query will not wonder what you really meant to do.

Re: proc SQL order by a negative date column?

CharlieSong — Sun, 20 Mar 2016 03:59:14 GMT

Could you tell me whether SAS allows to use like that?

Re: proc SQL order by a negative date column?

PGStats — Sun, 20 Mar 2016 04:05:18 GMT

Yes, you can ORDER BY a SQL expression. Check the Documentation.

Re: proc SQL order by a negative date column?

PGStats — Sun, 20 Mar 2016 04:47:28 GMT

Added explanation. SAS dates are just numbers. Under the hood, the date 01JAN2010 is just 18263. So -date is also just a number; -18263 when viewed as a date is 31DEC1909. When SAS is sorting by a SAS date, it is in fact just sorting numbers.

Hope this helps.

Re: proc SQL order by a negative date column?

CharlieSong — Sun, 20 Mar 2016 04:59:35 GMT

Hi,Thank you very much ! I understand it now.