Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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;


Accepted Solutions
Solution
‎08-11-2012 10:27 AM
Valued Guide
Posts: 765

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

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;

View solution in original post


All Replies
Solution
‎08-11-2012 10:27 AM
Valued Guide
Posts: 765

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

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;

Respected Advisor
Posts: 4,659

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

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

PG
Valued Guide
Posts: 765

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

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

Super Contributor
Posts: 338

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

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

Super Contributor
Posts: 276

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

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

Respected Advisor
Posts: 3,124

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

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;

Super Contributor
Posts: 276

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

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

Respected Advisor
Posts: 3,124

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

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

Super User
Posts: 9,691

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

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

Respected Advisor
Posts: 3,124

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

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

Super Contributor
Posts: 276

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

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

Regards..

Sanjeev.K

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 6384 views
  • 9 likes
  • 6 in conversation