topic Re: How to select last n rows of input records using SQL? in SAS Procedures

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

Mirisage — Sat, 11 Aug 2012 13:44:54 GMT

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;

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

MikeZdeb — Sat, 11 Aug 2012 14:27:57 GMT

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;

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

PGStats — Sat, 11 Aug 2012 18:14:47 GMT

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;

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

Haikuo — Sat, 11 Aug 2012 18:26:15 GMT

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

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

MikeZdeb — Sat, 11 Aug 2012 18:27:03 GMT

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

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

Mirisage — Sat, 11 Aug 2012 19:14:45 GMT

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

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

kuridisanjeev — Mon, 13 Aug 2012 10:09:25 GMT

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

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

kuridisanjeev — Mon, 13 Aug 2012 10:32:25 GMT

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

Regards..

Sanjeev.K

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

Haikuo — Mon, 13 Aug 2012 11:33:04 GMT

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;

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

kuridisanjeev — Mon, 13 Aug 2012 11:52:47 GMT

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

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

Haikuo — Mon, 13 Aug 2012 12:05:57 GMT

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

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

Ksharp — Tue, 14 Aug 2012 03:12:29 GMT

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