Select earliest record by field change

[podarum]

I have a list of records by account and looking to only capture data (earliest) when there is an interest rate change.

HAVE

Acct    Date     Rate

123     Jan1      2.3

123     Jan2      2.3

123     Jan3      3.4

123     Jan4      3.4

123     Jan5      3.4

123     Jan6      5

345     Jan1      1.2

345     Jan2      1.2

345     Jan3      2

345     Jan4      2.4

345     Jan5      2.4

345     Jan6      3

345     Jan7      3

and so on for many accts...

WANT  (only rate changes - including the first one)

Acct    Date     Rate

123     Jan1      2.3

123     Jan3      3.4

123     Jan6      5

345     Jan1      1.2

345     Jan3      2

345     Jan4      2.4

345     Jan6      3

Thank you

[Jagadishkatam · Accepted solution]

Please try the below code

proc sort data=have;
by acct rate date;
run;

data want;
set have;
by acct rate date;
if first.rate;
run;

[ballardw]

Assuming the data is sorted by acct and date.

data want;
   set have;
   by acct  notsorted rate;
   if first.rate;
run;

[Astounding]

I believe NOTSORTED applies to the preceding variable name.  If that's right, a slight variation is called for:

data want;
   set have;
   by acct rate notsorted;
   if first.rate;
run;

[ballardw]

---

@Astounding wrote:

I believe NOTSORTED applies to the preceding variable name.  If that's right, a slight variation is called for:

data want;
   set have;
   by acct rate notsorted;
   if first.rate;
run;

---

Either works:

From the documentation for Notsorted on a BY  statement:

The NOTSORTED option applies to all of the variables in the BY statement. You can specify the NOTSORTED option anywhere within the BY statement.

[hashman]

@podarum:

data have ;                    
  input Acct Date :$4. Rate ;  
  cards ;                      
123     Jan1      2.3          
123     Jan2      2.3          
123     Jan3      3.4          
123     Jan4      3.4          
123     Jan5      3.4          
123     Jan6      5            
345     Jan1      1.2          
345     Jan2      1.2          
345     Jan3      2            
345     Jan4      2.4          
345     Jan5      2.4          
345     Jan6      3            
345     Jan7      3            
;                              
run ;                          
                               
data want (drop = _:) ;        
  set have ;                   
  by acct ;                    
  _r = lag (rate) ;            
  if first.acct or _r ne rate ;
run ;                          

Kind regards

Paul D. 

[podarum]

Thanks everyone for the awesome responses.