topic Re: PROC SQL: Select records based on a variable value in the last record. in SAS Procedures

PROC SQL: Select records based on a variable value in the last record.

deleted_user — Mon, 26 Oct 2009 10:22:28 GMT

SAS table sorted by YEAR:
YEAR X Y Z
2000 2 7 5
2000 3 4 6
.
2001 1 2 7
.
2009 7 5 1 (last record)

Based on the value 2009 for YEAR in the last record I want to select all records with YEAR >= 2009 - 4, i.e. the records for the last 5 years.

The value for YEAR in the last record is not supposed to be known.

So if YEAR=2007 in the last record I would like to select all records with YEAR in [2003,2007]. How can this be done with PROC SQL? Message was edited by: ErnestoC

Re: PROC SQL: Select records based on a variable value in the last record.

DanielSantos — Mon, 26 Oct 2009 12:23:00 GMT

Assuming that data is sorted by YEAR (ascending), this would suit your needs:

proc sql noprint;
select max(YEAR) into :MAX_YEAR from DATA;
create table RESULT as
select * from DATA where &MAX_YEAR-3 le YEAR le &MAX_YEAR;
quit;

First, the target year is read into a macro var.
Then this value is used to filter the data accordingly.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: PROC SQL: Select records based on a variable value in the last record.

LinusH — Mon, 26 Oct 2009 15:17:19 GMT

If the table is large, you would might consider to use data step techniques to read the last record directly,
since the SQL select() max will result in a resource consuming table scan.

/Linus

Re: PROC SQL: Select records based on a variable value in the last record.

Peter_C — Tue, 27 Oct 2009 09:06:02 GMT

why ONLY SQL?
There must be some reason you could explain!
Because SQL does not acknowledge row-order, it is unable to take advantage of that information, which is available to a data step reading a SAS table.
Perhaps your undeclared reason might point to alternative approaches like dbms-specific opportunities.
Would you like to explain why you can't use a data step for this (it makes reading SAS data in reverse, easy).

PeterC