BookmarkSubscribeRSS Feed
lucky66
Calcite | Level 5

Hi All,

I have a table example below

  • In this table, there are 3 different accountIDs
  •  each accountID starts in different time and stay for various time periods
  • In each time, there are different rates

 

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:

  • For each accountID, at each time t, I need to get the minimum of historical rates as of time t-1.
  • For example, for a1,at time t 01/01/2000, as of t-1, the minimum historical rate is the same as current one. As there is  no historical rate before that.
  • However, for a1, when at time t=07/01/2000, the minimum historical rate as of t-1 04/01/2000 is 7.2.

Question:

Anyone has an idea how to write SAS code to realized it?

 

Thank you very much.

 

 

 

 

 

5 REPLIES 5
ballardw
Super User

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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.

Reeza
Super User

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;
lucky66
Calcite | Level 5

Thank you, everyone above for your replies. Very helpful, let me start with those.

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
  • 5 replies
  • 834 views
  • 0 likes
  • 5 in conversation