turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- how to get a as of t-1 minimum value for each cust...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-19-2016 10:37 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lucky66

05-19-2016 10:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lucky66

05-19-2016 10:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lucky66

05-19-2016 11:09 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lucky66

05-19-2016 11:11 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

05-19-2016 01:12 PM

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