topic Re: Select earliest record by field change in SAS Programming

Select earliest record by field change

podarum — Wed, 28 Aug 2019 20:53:33 GMT

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

Re: Select earliest record by field change

Jagadishkatam — Wed, 28 Aug 2019 20:57:47 GMT

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;

Re: Select earliest record by field change

ballardw — Wed, 28 Aug 2019 21:00:41 GMT

Assuming the data is sorted by acct and date.

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

Re: Select earliest record by field change

hashman — Wed, 28 Aug 2019 21:03:48 GMT

@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.

Re: Select earliest record by field change

Astounding — Thu, 29 Aug 2019 02:43:13 GMT

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;

Re: Select earliest record by field change

ballardw — Thu, 29 Aug 2019 14:52:38 GMT

@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.

Re: Select earliest record by field change

podarum — Thu, 29 Aug 2019 14:53:55 GMT

Thanks everyone for the awesome responses.