Sort by two variables and keep the last record after sort

[Sean_OConnor]

I need to sort by two variables to keep the most valid one to the bottom. I then need to output this record to a dataset. How do I go about this? See example which is failing.

All the best,

	*Sort dataset by the variable payslip_ref (unique payslip id) and also time_stamp (when record came into the system or changed in system);
	proc sort data=█
		by time_stamp payslip_ref ;

	*With the block sorted by payslip_reference and time_stamp we tell SAS to keep the last unique record and also output previous records;
	data latest_data obsolete_data;
		set █
		by payslip_ref;

		if last.payslip_ref then
			output latest_data;
		else output obsolete_data;

[Kurt_Bremser]

Sort

by payslip_ref time_stamp;

[Sean_OConnor]

Hi Kurt,

I would need the newest timestamp at the bottom 

i.e 24/4/2020 14:47

24/4/2020 14:48

So I would need to output 14:48

[Kurt_Bremser]

If your time_stamp is a SAS datetime, it will be the correct sort order. Just try it.

[Sean_OConnor]

I forgot to mention my timestamp is numeric.

This is the variable.  How do I increase the length to say 20 as my problem appears that is when it sorts it only takes into account the first 8 digits.

1576505690037

[Kurt_Bremser]

That number is not a SAS datetime value. Current datetime values have 10 digits (count of seconds from 1960-01-01T00:00:00):

 73         data _null_;
 74         now = datetime();
 75         put now=;
 76         put now= e8601dt20.;
 77         run;
 
 now=1903370562.5
 now=2020-04-24T18:02:43

Please post your data in a data step with datalines. Make sure that the code works and creates a dataset like yours.

[Kurt_Bremser]

PS even with your values, the sort works:

data have;
input payslip_ref $ time_stamp;
format time_stamp 20.;
datalines;
12345 1576505690037
12345 1576505690036
;

title "Pre Sort";

proc print data=have noobs;
run;

proc sort data=have;
by payslip_ref time_stamp;
run;

title "Post Sort";

proc print data=have noobs;
run;

Result:

Pre Sort
payslip_ref	time_stamp
12345	1576505690037
12345	1576505690036
Post Sort
payslip_ref	time_stamp
12345	1576505690036
12345	1576505690037

[ballardw]

---

@Sean_OConnor wrote:

I forgot to mention my timestamp is numeric.

This is the variable.  How do I increase the length to say 20 as my problem appears that is when it sorts it only takes into account the first 8 digits.

1576505690037

---

If you are sorting a numeric value, regardless of the display format assigned, the entire value is used for determining order.

However that value you show is NOT a SAS datetime value. Trimming of the last digit the result would be a datetime in the year 6955; 157650569003 => 01OCT6955:08:43:23. So you really need to very carefully describe just what sort of value that is supposed to be. And perhaps go back to how you brought that data into SAS so the value is a valid date time.

Or show us what your actual data set looks like. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[ballardw]

---

@Sean_OConnor wrote:

Hi Kurt,

I would need the newest timestamp at the bottom 

i.e 24/4/2020 14:47

24/4/2020 14:48

So I would need to output 14:48

---

Time values increase, so the largest value is the latest, i.e. "newest".

If the sort doesn't work then likely you need to insure that your timestamp is actually a date time value and not character.