how can we get the first and last observations in a dataset using Proc SQl?

[john83]

how can we get the first and last observations in a dataset using Proc SQl?

Thank you,

John

[art297 · Accepted solution]

John,

If your data set has a record number variable that indicates its position in the file, the task is trivial.  e.g.,

data have;

  set sashelp.class;

  recnum=_n_;

run;

proc sql;

  create table want as

    select *

      from have

        having recnum=min(recnum) or

          recnum=max(recnum)

  ;

quit;

If you don't have such a variable, the following works, but uses an undocumented function (i.e., monotonic) and violates the principal that sql isn't supposed to be dependent upon record order:

proc sql;

  create table want as

    select *

      from sashelp.class

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

          monotonic()=max(monotonic())-1

  ;

quit;

Message was edited by: Arthur Tabachneck Corrected minor non-code related typing error

[john83]

Thanks @art297.

[MikeZdeb]

hi ... if you do not have a record number on each observation and you'd rather not use unsupported monotonic(), how about ...

proc sql;

select count(*) into :lastobs from have;

create table want as

select * from have (obs=1)

union

select * from have (firstobs=&lastobs);

quit;

[Howles]

Here's a way which uses only documented features.

proc sql ;

ods output sql_results=numbered ;

ods listing close ;

reset number ;

select * from sashelp.class /* (obs=1) */ ;

reset nonumber ;

ods listing ;

ods output close ;

create table first_last(drop=row) as

select *

  from numbered

  having row EQ min(row)

union all

select *

  from numbered

  having row EQ max(row) ;

drop table numbered ;

quit ;

Note that this will generate two rows if the given data set has one row (test that by un-commenting the OBS= option). If that behavior is not desired, it's simpler; get rid of the UNION and combine, with OR, the MIN and MAX conditions.

[Ksharp]

Hpwles.

It is very interesting. I also found the following is working.

ods listing close ;
ods output sql_results=x ;
proc sql number ;
select * from sashelp.class ; 
quit;
ods listing ;

Ksharp

[MikeZdeb]

hi ... neat, so you could use ...

ods listing close ;

proc sql number ;

select count(*) into :lastobs from sashelp.class;

ods output sql_results=x (where=(row in (1, &lastobs)));

select * from sashelp.class ;

quit;

ods listing;

and only get one observation in the new data set when the old data set only has one observation

[sassimple]

hi..please find another example...

DATA cars1;
INPUT make $ model $ mpg weight price;
CARDS;
AMC Concord 22 2930 4099
AMC Pacer   17 3350 4749
AMC Spirit  22 2640 3799
Buick Century 20 3250 4816
Buick Electra 15 4080 7827
Cuick Electra 15 4080 7827
Cuick Century 20 3250 4816
Cuick Spirit  22 2640 3799

;
RUN;

proc sql;
create table test as
select make,model,mpg,weight,price,monotonic() as rownum,max(calculated rownum) as max,min(calculated rownum) as min,
case when calculated rownum=calculated max then 1 else 0 end as last_record,
case when calculated rownum=calculated min then 1 else 0 end as first_record
from cars1
group by make
order by rownum
;quit;

[Peter_C]

I must have mis-understood the question

First row is very easy (obs=1)

Last row not much more fuss (firstobs= &maxobs)

We can collect these data without going through the whole of the (potentially - very big data)

proc SQL ;

select nobs into :maxobs

from dictionary.tables

where libname='YOURLIB' and memname = 'YOUR_TABLE'

;

select * from yourLib.your_table( obs=1 )

union all

select * from  yourLib.your_table( firstobs= &maxobs )

;

quit ;

based on sashelp.class, the results viewer shows

Name      Sex       Age    Height    Weight

___________________________________________

Alfred    M          14        69     112.5

William   M          15      66.5       112

of course this speedy solution depends on YOURLIB.YOUR_TABLE being a SAS dataset

ok - did I misunderstand?

peterC