Proc SQL - to get first row and last 5 rows

[RamKumar]

I was wondering for Proc SQL code to generate the report with first row and last 5 rows from 20 rows.  Let me know if it is possible to achieve in single data step instead of creating multiple datasets.

[RW9]

Well, you can, don't know why you would want to do it this way though:

proc sql;
     create table WANT as
     select     *,
                    MONOTONIC() as ID
     from       SASHELP.CARS
     where     CALCULATED ID in (1,16,17,18,19,20);
quit;

Or if you don't know how many obs:

proc sql;
  select  NOBS
  into    :NO
  from    SASHELP.VTABLE
  where   LIBNAME="SASHELP"
    and   MEMNAME="CARS";
  create table WANT as
  select  *,
          MONOTONIC() as ID
  from    SASHELP.CARS
  where   CALCULATED ID=1 or CALCULATED ID >= (&NO.-4);
quit;

[BrunoMueller]

Find below a DATA Step example.

The first steps creates data for validation, the second steps has the actual row selection using a subsetting IF

data have;
  rowId + 1;
  set sashelp.Class;
run;

data want;
  set have nobs=_nObs;
  if _n_ = 1 or _n_ >= (_nObs - 4);
run;

[venkatnaveen]

can u explain this step

nobs=_nObs;
  if _n_ = 1 or _n_ >= (_nObs - 4);

I am not getting?

[BrunoMueller]

During compilation of the DATA step the number of observations is read from the input data set and stored in the variable _nObs.

During execution for each iteration of the DATA step loop _n_ is increased by one.

The IF without a THEN is called a Subsetting IF.

[venkatnaveen]

Thanks for that reply.

Now clarified.

I m  a beginner

[Peter_C]

Using features that are limited to sas datasets, here is an sql approach

%let table = sashelp.class;

Proc sql noprint ;

Select ( nobs-4) into :last_part from dictionary.tables

where libname = "%upcase(%scan(&table,1,.))"

And memname = "%upcase(%scan(&table,-1))"

;

Create table subset as

select * from &table( obs=1)

union all

select * from &table(firstobs=&last_part)

;

quit;

Limited to SAS datasets because probably only these support the firstobs=

[sas_lak]

/* Try this,  to get first record and 5th record from last */

proc sql;

create table first_1_last5_record (drop=SL_NO) as select

monotonic() as SL_NO,*  from HAVE

having monotonic()=min(monotonic(SL_NO)) or

       monotonic()=MAX(monotonic(SL_NO))-4

    ;

quit;

[jakarman]

Would not advice any approach using SQL for getting first and last records when you have no ordering.
SQL is designed for getting records out of a DBMS without being aware of any physical ordering.
By that the multi-threading (parallel processing) is allowed. The use of monotonic as hidden feature is for the same reason not reliable and should be avoided.

There are more design differences with SQL and SAS-datastep to be aware off. One of them is Null value and the SAS missing. These are having a different logical background. Often being confused as equal. 

See: http://en.wikipedia.org/wiki/Null_(SQL)

[RW9]

Yep, that's why I couldn't understand why he would want such an ad-hoc sample as obs xyz.  Doesn't make sense in any system.  Better to use some categorization and subset that way.  Its also not necessarily reproducible if needed to be re-run or validated. 

[Ksharp]

You need to generate an index variable like :

data class;
 set sashelp.class;
 n+1;
run;

proc sql;
select count(*) into : n from class;
select * from class where n in (  1 %eval(&n-4) : &n  ) ;
quit;

Xia Keshan

[jakarman]

Yes the obs and nobs features are very dedicated to SAS datasets. Then that is very dedicated solution not applicable with external RDBMS tables.
"How does color 9 smell."  If the question is not really a good one my preference is to ask why and go for the issue behind that. It is not always appreciated to do that. 

[Peter_C]

Jaap

where a poster requests sql without specifying which database is the target I presume SAS.

I belive my answer is as valid as the question. (pardon my very smug answer for what looks more like a teacher's test than any operational difficulty)

regards

Peter