BookmarkSubscribeRSS Feed
JonathanWarrick
Calcite | Level 5
I have a dataset that has a RATE variable and a DATE variable. I want to create a table that lists each unique RATE with the most recent DATE. For example, if I had 100 records that had various counts of 1%, 2%, 3%, 4%, 5% rates, the table that I would ultimately end up would be 5 records, 1 of each for each rate, with the corresponding date that was most recent. I've been struggling with an approach to code this, so any advice that will get my in the right direction would be appreciated.

I've thought about sorting the data by the RATE column and then DATE column (descending), getting the count of all the records with each rate, and then creating datasets for each rate using the OBS function (ie if the first rate occurred 62 times, I would create a dataset starting with OBS1 and then the next dataset with LASTOBS + 1), but there must be an easier way I haven't thought of.
4 REPLIES 4
Doc_Duke
Rhodochrosite | Level 12
Creating multiple tables seems like a vastly different problem. Addressing the question you posed in the first paragraph, using the data step approach.

PROC SORT; BY rate date; RUN;

DATA MostRecent;
SET;
BY rate date;
IF last.date;
RUN;

Would do it. You could do it in one CREATE statement with SQL, but it would internally still have to do the SORT.

Doc Muhlbaier
Duke
JonathanWarrick
Calcite | Level 5
I used the code Peter suggested and have run into a new problem. The first dataset I examined has 18 unique rate/date combos. All 18 of these combos have the same rate (0%) and obviously a different date. When I run the code Peter suggested, the 3,016 observations output 18 observations, representing each date with a rate of 0%. I want the result in this case to be in observation - 0% with the most recent date.

A little insight into what I need out of this analysis might help as well. The point of this analysis is to identify within each dataset if a business line is using multiple rates (which is prohibited). For reporting purposes, we want to identify all rates used and the most recent date at which they were used (business lines are allowed to change rates, but only at certain times). In most cases, we expect to see only one rate, so the resulting table for each business line should be only one observation.
Peter_C
Rhodochrosite | Level 12
JonathanW
OK,
sounds like you will have to sort your data (previously it was "Doc" who offered code).[pre]proc sort data= your.data out= lines ;
by business_line date ;
run ;
data exceptions( keep= business_line date rate ) ;
do until( last.business_line ) ;
set lines ;
by business_line rate notsorted ;
if last.rate and not last.business_line then do ;
problem_date= date ;
output ;
end ;
end ;
if problem_date then output ;
run ;[/pre]with some sample data I would be able to do appropriate testing.
Notice that I do not sort by RATE. If a business line uses rate1 then rate2 then rate1 again, the process I propose will have two rows in exceptions- first just before rate2 is introduced, and second with the last date when rate1 was used. I decided that model was appropriate because you seem very interested in the date of change, and not just "the last date used".

peterC
Peter_C
Rhodochrosite | Level 12
I would turn the rate table into a format based on returning the rate for dates in the range.
Alternatively:
If the date range is not more than a few years fill an array indexed on date with rate for each date. 10 years would need no more than 10 x 366 x 8 bytes. That is small amount of memory.
The advantage of these approaches - no need to sort the data.
peterC

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 656 views
  • 0 likes
  • 3 in conversation