topic Re: How can we keep the most recent data with proc sort? in SAS Programming

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

GN0001 — Fri, 10 Dec 2021 04:42:42 GMT

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

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

SASKiwi — Fri, 10 Dec 2021 05:53:45 GMT

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?

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

japelin — Fri, 10 Dec 2021 07:40:51 GMT

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;

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;

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

Kurt_Bremser — Fri, 10 Dec 2021 07:31:09 GMT

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?

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

ballardw — Fri, 10 Dec 2021 08:06:12 GMT

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

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

Kurt_Bremser — Fri, 10 Dec 2021 09:24:47 GMT

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

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

GN0001 — Sat, 11 Dec 2021 20:05:47 GMT

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;

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

Kurt_Bremser — Sat, 11 Dec 2021 20:12:39 GMT

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.

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

GN0001 — Sat, 11 Dec 2021 20:12:42 GMT

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

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

Kurt_Bremser — Sat, 11 Dec 2021 20:15:54 GMT

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

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

GN0001 — Sat, 11 Dec 2021 20:31:10 GMT

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

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

Kurt_Bremser — Sat, 11 Dec 2021 20:37:30 GMT

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

Or run a PROC SQL with

having date = max(date)

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

GN0001 — Sat, 11 Dec 2021 20:37:46 GMT

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

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

GN0001 — Sat, 11 Dec 2021 20:39:50 GMT

Dear team member,

Thanks for your post. Thanks for this approach. I can use proc sql to do get what I want. How about proc sort nodupkey approach?

Thanks,

blueblue

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

Kurt_Bremser — Sat, 11 Dec 2021 20:40:02 GMT

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

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

GN0001 — Sat, 11 Dec 2021 20:51:31 GMT

Hello Kurt,
I think this works if I sort ascending by date, no dupkey. I need to make sure which observation SAS keeps after I sort it by ascending.
if I have:
1 03/14/2021
1 04/14/2021
1 08/09/2021
Then when I use proc sql no dupkey ascending, then I will get 08/09/2021, SAS doesn't remove the max date? I want to know how SAS processes the observations in proc sort nodupkey ascending.
Please advise me.
Regards,
bluebue

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

Patrick — Tue, 14 Dec 2021 11:27:34 GMT

@GN0001 IF you would have posted sample data with the date variable containing a proper SAS date value then I assume the discussion here would have been much shorter/quicker.

IF you've got a date variable that sorts properly then a double Proc Sort is one way to go.

The first Proc Sort to order your data with the newest date per member id on top, the 2nd Proc Sort with a NODUPKEY to then pick the first row per member id (same that what a data step if first.member_id would do).

data have;
  input MemberID $ date1:ddmmyy10.;
  format date1 date9.;
  datalines;
1 04/02/2021
1 04/03/2021
1 05/03/2019
;

proc sort data=have out=want;
  by MemberID DESCENDING date1;
run;
proc sort nodupkey out=want;
  by MemberID;
run;

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

Kurt_Bremser — Sat, 11 Dec 2021 22:18:30 GMT

Using the double SORT may not work under certain circumstances (SPDE datasets, for example).

After the ascending sort, do

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

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

GN0001 — Sat, 11 Dec 2021 23:17:32 GMT

Hello Kurt, I found what I was looking for, so if it keeps the max in the first observation, this should work.
Thanks for your response.
By the way, I posted my data very first time I posted this question.
I truly appreciate your response.
Thanks,
Blue blue

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

GN0001 — Sat, 11 Dec 2021 23:19:54 GMT

Sorry, please see my response to Kurt.
Can you please read
My response to Kurt. Did you see what Kurt had responded?
Respectfully
Blueblue