topic Re: Proc SQL - to get first row and last 5 rows in SAS Procedures

Proc SQL - to get first row and last 5 rows

RamKumar — Thu, 17 Jul 2014 11:44:28 GMT

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.

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

RW9 — Thu, 17 Jul 2014 12:05:21 GMT

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;

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

BrunoMueller — Thu, 17 Jul 2014 12:14:09 GMT

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;

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

sas_lak — Thu, 17 Jul 2014 12:34:20 GMT

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

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

jakarman — Thu, 17 Jul 2014 12:58:42 GMT

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)

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

RW9 — Thu, 17 Jul 2014 13:16:26 GMT

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.

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

Ksharp — Thu, 17 Jul 2014 14:14:35 GMT

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

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

venkatnaveen — Fri, 18 Jul 2014 05:21:53 GMT

can u explain this step

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

I am not getting?

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

BrunoMueller — Fri, 18 Jul 2014 05:51:33 GMT

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.

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

venkatnaveen — Fri, 18 Jul 2014 06:23:13 GMT

Thanks for that reply.

Now clarified.

I m a beginner

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

Peter_C — Fri, 18 Jul 2014 21:44:02 GMT

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=

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

jakarman — Sat, 19 Jul 2014 04:49:37 GMT

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.

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

Peter_C — Sat, 19 Jul 2014 06:17:38 GMT

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