04-20-2018 04:25 PM
I have a quick question about sorting data by dates which are in character form. (eg. sorting data in ex dataset by usubjid and exstdtc ( character date) and finding first.obs or last.obs.)
Is it ok to sort data with out converting the exstdtc to numeric form and then find first.obs and last.obs (or) it is required to convert to numeric form and do the data step.
04-20-2018 04:31 PM
04-20-2018 04:32 PM
There's no reason for storing dates as character. Numeric dates can be stored in 4 bytes (less than any character date), and they can be used directly in date functions and with date formats. On top of that, they always sort in correct temporal order.
04-20-2018 05:01 PM
If you want to use first. and last. and not to create a numeric variable, you can try something like this:
PROC SQL; select *,case when INPUT(Date,DATE9.)=min(INPUT(Date,DATE9.)) then "First" When INPUT(Date,DATE9.)=max(INPUT(Date,DATE9.)) then "Last" else " " end as F_L from test Group by ID order by ID,INPUT(Date,DATE9.); quit;
04-20-2018 05:42 PM
What appearance do your current character-valued dates have?
About the only one that sorts correctly with regularity is a yyyymmdd or one of its variants where mm(month) and dd(day) have leading zeroes. 2017-2-18 would come after 2017-11-15, or 2018-2-9 2018-2-12 because character comparisons for sorting are done left to right
(the -2 is after -1 in the first case and -9 after -1 in the second). So to get those to sort properly you need 2017-02-18 2018-02-09 and 2018-02-12.
Two digits years are also a potential issue if the data crosses the century boundary: 990105 (5Jan1999) would come after 051212 (12Dec2005) as the 99 comes after 05.
If the dates are mmddyy then order would basically ignore year as 01012018 would come before 01022017.
if the dates are ddmmyy then you would have the first of all months then the second 01012018 01022018 01032018 then 0201208 (back to January).
Date9 or Date7 (05Jan2017) would have April and August before January, February and March...