topic Re: proc sql to select records from second row on in SAS Programming

proc sql to select records from second row on

Danglytics — Fri, 06 Jan 2012 15:18:16 GMT

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

,dol

from example_data

where id = 1 ; run ;

I just want :

ID DOL

1 8

1 10

1 3

Thanks for your help.

Re: proc sql to select records from second row on

MikeZdeb — Fri, 06 Jan 2012 15:41:10 GMT

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

Re: proc sql to select records from second row on

art297 — Fri, 06 Jan 2012 16:17:26 GMT

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.

Re: proc sql to select records from second row on

LarryWorley — Fri, 06 Jan 2012 16:17:58 GMT

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

Re: proc sql to select records from second row on

Reeza — Fri, 06 Jan 2012 17:31:22 GMT

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 ;

Re: proc sql to select records from second row on

Ksharp — Mon, 09 Jan 2012 06:18:45 GMT

Hello. Mike.

You should take a look at it.

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

Ksharp

Re: proc sql to select records from second row on

polingjw — Mon, 09 Jan 2012 11:23:42 GMT

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.