topic Re: Sort by two variables and keep the last record after sort in SAS Programming

Sort by two variables and keep the last record after sort

Sean_OConnor — Fri, 24 Apr 2020 13:32:14 GMT

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=&block;
		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 &block;
		by payslip_ref;

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

Re: Sort by two variables and keep the last record after sort

Kurt_Bremser — Fri, 24 Apr 2020 13:45:04 GMT

Sort

by payslip_ref time_stamp;

Re: Sort by two variables and keep the last record after sort

Sean_OConnor — Fri, 24 Apr 2020 13:48:24 GMT

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

Re: Sort by two variables and keep the last record after sort

Kurt_Bremser — Fri, 24 Apr 2020 14:17:10 GMT

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

Re: Sort by two variables and keep the last record after sort

ballardw — Fri, 24 Apr 2020 14:33:59 GMT

@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.

Re: Sort by two variables and keep the last record after sort

Sean_OConnor — Fri, 24 Apr 2020 15:17:38 GMT

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

Re: Sort by two variables and keep the last record after sort

Kurt_Bremser — Fri, 24 Apr 2020 16:03:20 GMT

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.

Re: Sort by two variables and keep the last record after sort

Kurt_Bremser — Fri, 24 Apr 2020 16:05:15 GMT

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

Re: Sort by two variables and keep the last record after sort

ballardw — Fri, 24 Apr 2020 16:08:04 GMT

@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-data-AKA-generate/ta-p/258712 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.