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

[Mirisage]

Apologize if multiple postings occured......

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.

proc sql (inobs=5); 

    create table post_this

     as  select bank_number     ,

current_date    ,

account_number  ,

balance

     from dups

     ;

quit;

[MikeZdeb · Accepted solution]

hi ... one way ...

proc sql;

select nobs into :nobs from dictionary.tables where libname eq 'WORK' and memname eq 'DUPS';

create table post_this as

select bank_number, current_date, account_number, balance

from dups (firstobs=%eval(&nobs-4) obs=&nobs);

quit;

[PGStats]

Mike's proposal, simplified a bit :

proc sql noprint;

select max(1, nobs-4) into :firstobs from dictionary.tables where libname eq 'WORK' and memname eq 'DUPS';

create table post_this as

select bank_number, current_date, account_number, balance

from dups (firstobs=&firstobs);

reset print;

select * from post_this;

quit;

PG

[MikeZdeb]

hi ... yes I agree, it's overkill using "OBS=the last observation number" since that's the default behavior (that was a "DUH" moment)

[Mirisage]

Hi MikeZdeb, PGStats and Hai.kuo,

Thank every one of you.

All 3 codes are nicely working for my large dataset. (Just deleted one “select” of Hai).

Every time I get many new things to learn in each code. SAS seems such a massive area.

Thanks again!

Mirisage

P.S. There is no way to "click" "correct answer" button for more than one response

[kuridisanjeev]

PG's proposal, simplified a bit 🙂

proc sql;

select ((count(*)-4)) into :cnt from dups ;

create table post_this as

select*

from dups (firstobs=&cnt);

reset print;

select * from post_this;

quit;

Regards..

Sanjeev.K

[Haikuo]

Hi,Sanjeev.K

Your code is simpler by its looks, but NOT in practice. PG's code is more efficient since only metadata is involved. Picture if you have million records in 'dups', and how long it would take for 'count(*)' to process?

Haikuo

UPdate:

If you really want to get the obs count directly from 'dups', there is a better way, not a single record will need to be read by doing so.

data _null_;

call symputx('cnt',nobs);stop;

set have nobs=nobs;

run;

[kuridisanjeev]

Hi Hai.Ku..

Hmm yes i do agree with you on this.Actually i am  thinking about the simplifying  code and not considering about huge data..

Thank you for correcting me ..and one more clarification i need on  PG's code..

Is there any reason for using MAX function??Instead  of MAX function we can simply use like 

select (nobs-4) into :firstobs right???

Regards..

Sanjeev.K

[Haikuo]

what if your data set containing 4 or less obs? then  (firstobs=&cnt) will cause error. PG's code is to have that covered.

Haikuo

[Ksharp]

HaiKuo,

One of advantages of Sanjeev.K's code is he can pick up the real nobs of a dataset. whereas, metadata nobs from dictionary table is not real nobs . For example:

data class;set sashelp.class;run;

data class;

modify class;

if sex  eq 'M'  then remove;

run;

proc sql;

select nobs from dictionary.tables where libname='WORK' and memname='CLASS';

quit;

it is still 19.

But we can use nlobs instead of nobs to rectify this problem.

select nlobs from dictionary.tables where libname='WORK' and memname='CLASS';

Ksharp

[Haikuo]

How about something undocumented:

proc sql;

create table post_this as

select select bank_number, current_date, account_number, balance

from dups having max(monotonic())-4 <= monotonic()<= max(monotonic());

quit;

Haikuo

[kuridisanjeev]

Little bit confusing but great skills..liked it...

Regards..

Sanjeev.K