Hi All,
I have a table example below
AccountID | Date(t) | Rate | AsofDateminus1(t-1)minrate |
a1 | 01/01/2000 | 7.423333 | 7.423333327 |
a1 | 04/01/2000 | 7.2 | 7.423333327 |
a1 | 07/01/2000 | 7.553333 | 7.2 |
a1 | 10/01/2000 | 7.1 | 7.2 |
a1 | 01/01/2001 | 7.343333 | 7.1 |
a1 | 04/01/2001 | 7.406667 | 7.1 |
a1 | 07/01/2001 | 7.43 | 7.1 |
a1 | 10/01/2001 | 7.446667 | 7.1 |
a1 | 01/01/2002 | 7.64 | 7.1 |
a2 | 07/01/2000 | 7.553333 | 7.553333441 |
a2 | 10/01/2000 | 7.1 | 7.553333441 |
a2 | 01/01/2001 | 7.343333 | 7.1 |
a2 | 04/01/2001 | 7.406667 | 7.1 |
a2 | 07/01/2001 | 7.43 | 7.1 |
a2 | 10/01/2001 | 7.446667 | 7.1 |
a2 | 01/01/2002 | 7.64 | 7.1 |
a3 | 01/01/2004 | 8.8733333 | 8.873333295 |
a3 | 04/01/2004 | 8.9833333 | 8.873333295 |
a3 | 07/01/2004 | 9.1566668 | 8.873333295 |
a3 | 10/01/2004 | 8.8633337 | 8.873333295 |
a3 | 01/01/2005 | 8.7800001 | 8.863333702 |
Task:
Question:
Anyone has an idea how to write SAS code to realized it?
Thank you very much.
Assuming the data is sorted by ID and date
data want;
set have;
by id notsorted;
retain minrate;
if first.id then minrate= rate;
else minrate = min(minrate,rate);
run;
should get you started
Well, not typing in test data so just going to give some pointers here. There are several ways to do this.
- You could transpose the data up and use arrays.
- You could retain values into a set of three variables.
- You could use lag() function to look back, i.e. i
result=min( ifn(lag(accountid)=accountid),lag(rate),999999), ifn(lag2(accountif)=accountid),lag2(rate),999999), ...same for lag3());
So if there is no previous then 99999 is used which will obviously not be the min.
- You could also merge on previous values to each row.
The options are limitless.
I think ballardw has the right approach, but it will take another trick to get correct results.
data want;
set have;
by ID;
retain minrate;
if first.ID then minrate = rate;
output;
minrate = min(minrate, rate);
run;
The same assumptions are in effect ... data must be in sorted order by ID and date.
Assuming you don't want to include the current record in the calculation of the minimum you need to control the calculation and when the values are output. Untested, and building of @ballardw code:
data want;
set have;
by id notsorted;
retain minrate;
if first.id then minrate= rate;
output; *Explicitly output before calculation to hold last value;
minrate = min(minrate,rate);
run;
Thank you, everyone above for your replies. Very helpful, let me start with those.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.