Help using Base SAS procedures

Proc SQL - to get first row and last 5 rows

Reply
Regular Contributor
Posts: 168

Proc SQL - to get first row and last 5 rows

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.

Super User
Super User
Posts: 7,392

Re: Proc SQL - to get first row and last 5 rows

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;

SAS Super FREQ
Posts: 682

Re: Proc SQL - to get first row and last 5 rows

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;
Frequent Contributor
Posts: 89

Re: Proc SQL - to get first row and last 5 rows

can u explain this step

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


I am not getting?

SAS Super FREQ
Posts: 682

Re: Proc SQL - to get first row and last 5 rows

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.

Frequent Contributor
Posts: 89

Re: Proc SQL - to get first row and last 5 rows

Thanks for that reply.

Now clarified.

I m  a beginner

Valued Guide
Posts: 2,174

Re: Proc SQL - to get first row and last 5 rows

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=

Contributor
Posts: 46

Re: Proc SQL - to get first row and last 5 rows

/* 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;

Valued Guide
Posts: 3,208

Re: Proc SQL - to get first row and last 5 rows

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)

---->-- ja karman --<-----
Super User
Super User
Posts: 7,392

Re: Proc SQL - to get first row and last 5 rows

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. 

Super User
Posts: 9,671

Re: Proc SQL - to get first row and last 5 rows

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

Valued Guide
Posts: 3,208

Re: Proc SQL - to get first row and last 5 rows

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. 

---->-- ja karman --<-----
Valued Guide
Posts: 2,174

Re: Proc SQL - to get first row and last 5 rows

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

Ask a Question
Discussion stats
  • 12 replies
  • 3981 views
  • 0 likes
  • 8 in conversation