How can we keep the most recent data with proc sort?

[GN0001]

Hello team,

I have a project that I need the most recent date. How can I use proc sort nodupkey take care of this matter?

What other approaches we have for keep only the most recent date?

If our date to be character date, does proc sort sort our data?

Your help is greatly appreciated.

blue blue

[SASKiwi]

Character dates in SAS are just text strings so they will sort in alphabetical order. You need proper SAS dates which are numeric variables with SAS date formats applied if you want your data to sort correctly in date order.

Please provide a sample of your data if you want to confirm your date selection rules. What happens with duplicate dates? Which date record will you choose?

[ballardw]

---

@SASKiwi wrote:

Character dates in SAS are just text strings so they will sort in alphabetical order. You need proper SAS dates which are numeric variables with SAS date formats applied if you want your data to sort correctly in date order.

Please provide a sample of your data if you want to confirm your date selection rules. What happens with duplicate dates? Which date record will you choose?

---

@SASKiwi I think you meant "calendar order" or "chronological order" as they will sort in alphabetical order which often does not match calendar order.

[Kurt_Bremser]

Character "dates" in ISO8601 format would at least sort correctly, but (@GN0001) they are still useless for any type of calculation.

[GN0001]

Hello team member,

This is my code:

Data test1;
Input MemberID $ date1;
datalines
1 04/02/2021
1 04/03/2021
1 05/03/2019
;
Run;

I want to use proc sort to keep one meberID with the max date. If I sort the data with proc sort with nodupkey, how can I get to the point that I can keep one memberId with Max Date? 

Please let me know.

Respectfully,

blue blue

[Kurt_Bremser]

---

@GN0001 wrote:

Hello team member,

This is my code:

Data test1;
Input MemberID $ date1;
datalines
1 04/02/2021
1 04/03/2021
1 05/03/2019
;
Run;

I want to use proc sort to keep one meberID with the max date. If I sort the data with proc sort with nodupkey, how can I get to the point that I can keep one memberId with Max Date? 

Please let me know.

Respectfully,

blue blue

---

Run this code, inspect the log, and fix it, as a simple first lesson.

[japelin]

The first step, though, is to fix the text date to a number.

you can use proc sort with nodupkey and last. variable.

data have;/* sample data  */
  do id=5 to 1 by -1;
    do i=20010 to 20000 by -1;
      dt=i+int(ranuni(i)*100);
      output;
    end;
  end;
  format dt yymmdd10.;
  drop i;
run;

proc sort data=have nodupkey;
  by id dt;
run;

data want;
  set have;
  by id dt;
  if last.id;
run;

or

Use proc sql

proc sql;
  select * from have as a 
  where dt=(select MAX(dt) from have as b 
            WHERE a.id=b.id);
quit;

[GN0001]

Hello team member,
Can you please explain about this? In the code below, 20010 to 20000 are not dates!
data have;/* sample data */
do id=5 to 1 by -1;
do i=20010 to 20000 by -1;
dt=i+int(ranuni(i)*100);
output;
end;
end;
format dt yymmdd10.;
drop i;
run;

[Kurt_Bremser]

Of course they are dates in SAS. Dates are counts of days, with 1960-01-01 as day zero. Do this for reference:

data _null_;
date = today(),
put date=;
run;

and look at the log.

[GN0001]

Hello,
Thanks for your posts. I will try it, but I don't know how my question is related to what you have posted.
My boss told me proc sort data with memberID and keep the most recent date. I don't know how proc sort with nodupkey can bring the results he wants. I am not very willing to ask him how.
Respectfully,
blueblue

[Kurt_Bremser]

SORT on its own can't give you the max value, you need a follow-up step that selects this observation.

[GN0001]

This syntax didn't work: 

A subquery can not select more than one column... usually a subquery is used incorrectly in an expression

proc sql;
  select * from have as a 
  where dt=(select MAX(dt) from have as b 
            WHERE a.id=b.id);
quit;

How can I fix this issue?

Regards,

Blueblue

[Kurt_Bremser]

Character dates are useless, you have to convert them first.

Do you you only want the date for later use, or the observation(s) with the latest date?

[GN0001]

Hello,

I need a unique observation with a max date?

The date is already a number data type. I posted a dataline statement that shows date is a number.  I do understand the date as a character is useless.

Respectfully,

Blueblue

[Kurt_Bremser]

Sort by descending date, and keep the first observation in a data step.

Or run a PROC SQL with

having date = max(date)