How to find last record using proc sql

[BrahmanandaRao]

Hi Guys  

Good Morning  

How to find last record in a dataset using proc sql 

data last ;
set sashelp.class end=last;
if last then output;
proc print;run;

[Tom]

---

@BrahmanandaRao wrote:

Hi Guys  

Good Morning  

How to find last record in a dataset using proc sql 

data last ;
set sashelp.class end=last;
if last then output;
proc print;run;

---

The concept of order of observations is foreign to SQL.  It is a set operation language.

[Kurt_Bremser]

The correct answer is, as usual, Maxim 14.

SQL has no out-of-the-box tools for sequences, so you use the data step for such operations.

You can try a trick using the undocumented MONOTONIC function:

proc sql;
create table want (drop=m) as
  select
    *,
    monotonic() as m
  from sashelp.class
  having m = max(m)
;
quit;

(untested, posted from my tablet)

But I would NEVER use this in real life code.

[BrahmanandaRao]

Thanks for your solution

[s_lassen · Accepted solution]

SQL by design does not care about the record order. So there is no inherent way to get the "last" record. You may "cheat" by using the MONOTONIC function, but there is no guarantee the records will be retrieved in the order they have on the table.

Instead, you can do your actual example like this:

proc sql;
  create table last as 
  select * from sashelp.class
  having name=max(name)
  ;
quit;

As the data in SASHELP class are sorted by NAME.

Another way to "cheat" is to use the FIRSTOBS option:

proc sql noprint;
  select nobs into :nobs from dictionary.tables
  where libname='SASHELP' and memname='CLASS';
  create table last as 
  select * from sashelp.class(firstobs=&nobs);
quit;

which is a lot more efficient than using MONOTONIC, if you only want to get the last observation, and also faster than my first example.

The same thing can be done in a data step like this:

Data last;
  set sashelp.class nobs=nobs point=nobs;
  output;
  stop;
run;

and this is also a lot faster than reading the whole table like in your example.