DATA Step, Macro, Functions and more

proc sql to select records from second row on

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

proc sql to select records from second row on

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.


Accepted Solutions
Solution
‎01-06-2012 11:17 AM
Frequent Contributor
Posts: 129

Re: proc sql to select records from second row on

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

View solution in original post


All Replies
Valued Guide
Posts: 765

Re: proc sql to select records from second row on

Posted in reply to Danglytics

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

PROC Star
Posts: 7,474

Re: proc sql to select records from second row on

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.

Solution
‎01-06-2012 11:17 AM
Frequent Contributor
Posts: 129

Re: proc sql to select records from second row on

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

Super User
Posts: 19,815

Re: proc sql to select records from second row on

Posted in reply to LarryWorley

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 ;

Super User
Posts: 10,035

Re: proc sql to select records from second row on

Hello.   Mike.

You should take a look at it.

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

Ksharp

Regular Contributor
Posts: 171

Re: proc sql to select records from second row on

Posted in reply to Danglytics

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1550 views
  • 0 likes
  • 7 in conversation