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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LarryWorley
Fluorite | Level 6

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

6 REPLIES 6
MikeZdeb
Rhodochrosite | Level 12

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
Opal | Level 21

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
Fluorite | Level 6

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

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

Hello.   Mike.

You should take a look at it.

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

Ksharp

polingjw
Quartz | Level 8

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 8069 views
  • 0 likes
  • 7 in conversation