12-27-2014 12:35 PM
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.
12-27-2014 01:48 PM
create table want as
select *,monotonic() as cnt from have
having cnt between max(cnt)-9 and max(cnt);
12-27-2014 03:34 PM
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.
12-27-2014 03:40 PM
There isn't an official or supported way to do this.
12-27-2014 03:59 PM
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:
select max(1, nlobs - 10) into :nlobs
where libname="SASHELP" and memname="CLASS"; /* Use UPPERCASE names */
select * from sashelp.class(firstobs=&nlobs);
Disclaimer: not tested with U Edition.
12-27-2014 04:03 PM
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:
do i=1 to 20;
set have nobs=nobs;
if _n_>nobs-10 then output;
12-27-2014 04:15 PM
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:
do n=nobs-9 to nobs;
set have nobs=nobs point=n;
12-28-2014 02:39 AM
@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.
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!
Better tell the complete correct information.
12-28-2014 10:38 AM
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 ;
having serial GT max(serial)-10 ;
* Clean up ;
drop table driver table incremented ;