BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Doug____
Pyrite | Level 9

If I have a set of values ordered by ID and date, how do I determine the minimum value for each record at from all prior dates? I've tried using RETAIN but don't seem to be obtaining the correct results.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Here's an approach that assumes you do NOT want the current value considered when computing the PRIOR minimum:

 

proc sort data=have;

by id date;

run;

 

data want;

set have;

by id date;

if first.id then min_val = .;

retain min_val;

output;

min_val = min(min_val, varname);

run;

View solution in original post

3 REPLIES 3
Astounding
PROC Star

Here's an approach that assumes you do NOT want the current value considered when computing the PRIOR minimum:

 

proc sort data=have;

by id date;

run;

 

data want;

set have;

by id date;

if first.id then min_val = .;

retain min_val;

output;

min_val = min(min_val, varname);

run;

Doug____
Pyrite | Level 9

This method works well for what I'm doing. Any variations for when missing values appear in the series?

Astounding
PROC Star

The MIN function ignores missing values.  So I'm assuming you would like to add the possibility that the calculated minimum value comes out missing if one of the incoming values is missing.  That's slightly harder but not a great deal different.  After sorting:

 

data want;

set have;

by id date;

retain min_val;

if first.id then do;

   min_val = .;

   output;

   min_val = 1e17;

end;

else output;

if varname < min_val then min_val = varname;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1602 views
  • 0 likes
  • 2 in conversation