BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
john83
Calcite | Level 5

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

Thank you,

John

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

View solution in original post

8 REPLIES 8
art297
Opal | Level 21

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

MikeZdeb
Rhodochrosite | Level 12

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
Quartz | Level 8

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
Super User

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
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 58403 views
  • 2 likes
  • 7 in conversation