Learning SAS? Welcome to the exclusive online community for all SAS learners.

Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

Reply
Occasional Contributor
Posts: 17

Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

Hello Experts,

I am not able to display last 10 obs in a dataset using proc sql when I don't know how many obs are there in the dataset. I have done this using data step but I am unable to do the same using proc SQL.

Pleas help.

Trusted Advisor
Posts: 1,204

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

proc sql;

create table want as

select *,monotonic() as cnt from have

having cnt between max(cnt)-9 and max(cnt);

quit;

Valued Guide
Posts: 3,208

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

The SQL concept does support the concept. Please do not suggest questions and solutions violating that principle.

Sooner or later you will being hurt by that.

---->-- ja karman --<-----
Super User
Posts: 17,912

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

There isn't an official or supported way to do this. 

Respected Advisor
Posts: 4,659

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

It can be done once your data library is known to SAS (i.e. once you have defined your library with the LIBRARY statement). Here is an example using the SASHELP library which is defined by SAS:

proc sql;

select max(1, nlobs - 10) into :nlobs

from dictionary.tables

where libname="SASHELP" and memname="CLASS";  /* Use UPPERCASE names */

select * from sashelp.class(firstobs=&nlobs);

quit;

Disclaimer: not tested with U Edition.

PG

PG
Frequent Contributor
Posts: 115

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

HI, if your objective is just to learn and practice different ways of accomplishing stuff, that's fine to experiment. However, i'd rather avoid sql just not to bother using a function to process and so on. Since, that you have done this in datastep, My bet would still be with the datastep as I suppose you would have used something like is:

data have;

do i=1 to 20;

output;

end;

run;

data want;

set have nobs=nobs;

if _n_>nobs-10 then output;

run;

proc print;

run;

Respected Advisor
Posts: 3,124

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

if using data step, I would suggest direct access instead of sequential access, therefore all you need to read in and output is 10 obs regardless of size of incoming table:

data want;

do n=nobs-9 to nobs;

set have nobs=nobs point=n;

output;

end;

stop;

run;

Haikuo

Valued Guide
Posts: 3,208

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

@Pgtasts, Still very dedicated to a special situation and not a very good advice.

It is working with Proc SQL only because it is local SAS-dataset. For that type with that limitation a sas-datastep is a better approach.

You have coded:

- nobs usage                  Issue:  only applicable with the internal sas-datasets

- sashelp.library usage. Issue: every call will retrieve all librefs and datasets limiting to the needed information is done on the last moment

     It makes this approach unusable when there are many external RDBMS connected to your session.

- using the selection on the SAS-dataset that has the ordered defined property and using sas-dateset options , again very dedicated to a SAS (twice)

    It makes this approach unusable when the table is an external RDBMS type.

There are functions in an external RDBMS like lag/lead ORACLE-BASE - LAG and LEAD Analytic Functions and Window Functions in SQL

These are being used when having an ordered subset. For big data that kind of approaches are bad ones. Following the solutions you will find there is a need to make a additional copy of the to be worked on. That additional copy must be stored somewhere. A same kind of loophole as the usage of the Cartesian product. Also to be avoided.

These frunctions are causing confusion learning SAS as the LAG function in SAS does exist but is not the same functionality. It is a QUEUE function!

http://en.wikipedia.org/wiki/Queue_(abstract_data_type)

Better tell the complete correct information.

---->-- ja karman --<-----
Regular Contributor
Posts: 184

Re: Using PROC SQL, how to display last 10 obs from a dataset when we do not know how many observations are there in the dataset.

Try something like this:

proc sql ;

* Prepare a one-row driver ;

create table driver ( dummy char(0) ) ;

insert into driver set dummy = '' ;

* Generate serial numbers ;

reset noprint ;

select dosubl( cats( 'data incremented ;    ',

                     'Serial + 1 ;          ',

                     'set sashelp.class ;   ',

                     'run ;                 '

                   )

             )

  from driver ;

reset print ;

* Produce the report ;

select *

from incremented

  having serial GT max(serial)-10 ;

* Clean up ;

drop table driver table incremented ;

quit ;

Ask a Question
Discussion stats
  • 8 replies
  • 743 views
  • 2 likes
  • 8 in conversation