BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

11 REPLIES 11
MikeZdeb
Rhodochrosite | Level 12

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
Opal | Level 21

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
MikeZdeb
Rhodochrosite | Level 12

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
Obsidian | Level 7

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
Quartz | Level 8

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
Onyx | Level 15

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
Quartz | Level 8

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
Onyx | Level 15

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
Super User

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
Onyx | Level 15

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
Quartz | Level 8

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

Regards..

Sanjeev.K

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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