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;
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;
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;
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
hi ... yes I agree, it's overkill using "OBS=the last observation number" since that's the default behavior (that was a "DUH" moment)
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
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
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;
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
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
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
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
Little bit confusing but great skills..liked it...
Regards..
Sanjeev.K
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.