topic Re: How to find last record using proc sql in SAS Programming

How to find last record using proc sql

BrahmanandaRao — Mon, 31 May 2021 04:41:41 GMT

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;

Re: How to find last record using proc sql

Tom — Mon, 31 May 2021 04:47:21 GMT

@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.

Re: How to find last record using proc sql

Kurt_Bremser — Mon, 31 May 2021 04:52:28 GMT

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.

Re: How to find last record using proc sql

s_lassen — Mon, 31 May 2021 06:35:00 GMT

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.

Re: How to find last record using proc sql

BrahmanandaRao — Mon, 31 May 2021 06:49:16 GMT

Thanks for your solution