How to select last n rows of input records using SQL?

Reply
Super Contributor
Posts: 338

How to select last n rows of input records using SQL?

Hi Colleagues,

Could you please help me to select last 5 rows of input records using SQL?

I used “INOBS=” statement but it selects first five rows.

Also, are there any possbility to skip some rows from top and bottom and select only some wanted rows in the middle like (firstobs=5 obs=10) in data step?

proc sql (inobs=5)

    create table post_this

     as  select bank_number     ,

current_date    ,

account_number  ,

balance

     from dups

     ;

quit;

Thank you for your help

Mirisage

PROC Star
Posts: 7,467

Re: How to select last n rows of input records using SQL?

There is an undocumented (thus not recommended) sql function called monotonic() that COULD be used.  However, since you can use datastep options in proc sql, how about something like:

proc sql noprint;

  select count(*) into

    :total_records

      from sashelp.class

  ;

  create table want as

    select name

      from sashelp.class (firstobs=%eval(&total_records-4))

  ;

quit;

Respected Advisor
Posts: 4,173

Re: How to select last n rows of input records using SQL?

Just as a variation to Art's suggestion:

proc sql;

create table want as

  select name

    from sashelp.class (firstobs=%eval(%sysfunc(attrn(%sysfunc(open(sashelp.class)),nlobs))-4))

  ;

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 680 views
  • 0 likes
  • 3 in conversation