proc sql to select records from second row on

[Danglytics]

Hi,

I’m using proc sql and selecting all records from an ID

But I only want to start from the second record on, so skip the first transaction.

Example of data:

ID DOL

1 5

1 8

1 10

1 3

2 10

3 4

etc.

proc sql ;

select

id

,dol

from example_data

where id = 1 ; run ;

I just want :

ID DOL

1 8

1 10

1 3

Thanks for your help.

[MikeZdeb]

hi ... could try unsupported monotonic()

data x;

input id dol @@;

datalines;

0 9 1 5 1 8 1 10 1 3 2 10 3 4

;

proc sql;

select *

from x

where id eq 1 and monotonic() gt 1;

quit;

id       dol

1         8

1        10

1         3

[art297]

Mike's suggestion will "probably" do what you want, but there is ABSOLUTELY NO GUARANTEE!  By definition, sql does not consider the order of records in its processing.  With SAS proc sql it HAS in every situation where I have used the monotonic function, but it may not.

Of course, you could always assign a record number via a datastep, and then use the record number to guarantee selections via proc sql.

[LarryWorley · Accepted solution]

Or you could pre-process in data step with by processing to add an indicator of the first record in each id group. See http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000761931.htm for documentation on this.

This has the advantage of being supported but does require an additional data step.  Using Mike's example:

data x;  

  input id dol @@;

datalines;

0 9 1 5 1 8 1 10 1 3 2 10 3 4

;

data x_with_indicator ;

   set x ;

   by id ;

/*  add 0/1 indicator for first record in group */

   first_indicator = first.id ; 

   run ;

proc sql;

   select *

     from x_with_indicator

    where id eq 1

       and first_indicator ne  1

   ;

  quit;

                      first_id   dol    indicator

                          1        8            0

                          1       10           0      

                          1        3            0

[Reeza]

Or just do it in a datastep, since you need to pre-process it there.

data x;  

  input id dol @@;

datalines;

0 9 1 5 1 8 1 10 1 3 2 10 3 4

;

data x_with_indicator ;

   set x ;

   by id ;

     if not first.id;

   run ;

[Ksharp]

Hello.   Mike.

You should take a look at it.

http://support.sas.com/kb/15/138.html

Ksharp

[polingjw]

Can't you just use the FIRSTOBS= data set option?

proc sql;

     select id, dol

     from example_data(firstobs=2)

     where id=1;

quit;

Just note that the where clause is used to subset the data set before the FIRSTOBS= data set option is applied.