topic Re: how can we get the first and last observations in a dataset using Proc SQl? in SAS Programming

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

john83 — Sat, 14 Jan 2012 08:28:39 GMT

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

Thank you,

John

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

art297 — Sat, 14 Jan 2012 15:07:17 GMT

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

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

john83 — Sat, 14 Jan 2012 16:58:04 GMT

Thanks @art297.

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

MikeZdeb — Sat, 14 Jan 2012 19:27:26 GMT

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;

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

Howles — Sun, 15 Jan 2012 16:13:09 GMT

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.

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

Ksharp — Mon, 16 Jan 2012 05:06:30 GMT

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

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

MikeZdeb — Tue, 17 Jan 2012 16:47:37 GMT

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

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

sassimple — Thu, 28 Mar 2013 20:04:58 GMT

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;

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

Peter_C — Fri, 29 Mar 2013 02:16:02 GMT

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