Choosing a Particular Record Using a Prompt/Parameter

[paulrockliffe]

I have a dataset that holds a number of rows of data about each Entity, the data is sorted in date order and I am using a programme to split the first record and the last record into their own tables.  I then recombine these tables as one, with one row per entity and pairs of columns.  This allows me to run comparisons and identify changes over time. 

The code I use uses First and Last, which is dead simple, but I have a requirement to bring more data into my process, but to run the comparison over the same time period, so probably still First, but not Last anymore.  I want to use a Prompt to define the row to select for the comparison, as I have different user groups that want to compare over different time periods.

My codes is:

data Earliest_Year;
set analysis;
by Customer;
if Last.Customer;
run;

How can I change "if Last.Customer;" to something like "If Offset(Last.Customer, -1*&Prompt)"  if that makes sense?

Thanks!

[FredrikE]

How about sorting the data in reverse order?

proc sort data=Earliest_Year;

 by descending Customer;

run;

data Earliest_Year;
set analysis;
by descending Customer;
if First.Customer then counter = 0;

counter + 1;

if counter = &prompt OR last.customer then .......[do your comparisons] ;

run;

[paulrockliffe]

That could work. I realised the easiest way was to add a Query that filters the table to only include the rows defined by the prompts, and point the code at that. That way First and Last are still consistent with the data, but I've still got all my data for the rest of the process.