Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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
Super Contributor
Posts: 474

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

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
Super User
Posts: 5,256

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

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
Data never sleeps
Valued Guide
Posts: 2,175

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

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
Ask a Question
Discussion stats
  • 3 replies
  • 114 views
  • 0 likes
  • 4 in conversation