topic Re: Selecting the 2nd occurance in SAS Programming

Selecting the 2nd occurance

Timbim — Wed, 28 Aug 2019 05:13:32 GMT

Hi Community,

Could you please help me on how to select only the 2nd occurrence from a dataset? Below is an example. Kind regards

Account	InterestRate	InterestRate_ChangeDate
20001562	4.89%	19-Nov-18
20001562	4.55%	22-Nov-18
20006956	5.27%	11-Sep-18
20006956	5.25%	1-Nov-18
20006956	5.27%	8-Nov-18
20009898	5.43%	18-Sep-18
20009898	5.44%	18-Oct-18
20009898	5.55%	18-Nov-18
20009898	5.61%	18-Dec-18
20009898	5.00%	18-Jan-19

Re: Selecting the 2nd occurance

PeterClemmensen — Wed, 28 Aug 2019 05:23:44 GMT

Do you mean simply the second observation? Or the second occurrence inside each account?

Re: Selecting the 2nd occurance

PeterClemmensen — Wed, 28 Aug 2019 06:31:20 GMT

data have;
input Account InterestRate :percent. InterestRate_ChangeDate :date11.;
format InterestRate percent8.2 InterestRate_ChangeDate date11.;
datalines;
20001562 4.89% 19-Nov-18
20001562 4.55% 22-Nov-18
20006956 5.27% 11-Sep-18
20006956 5.25% 1-Nov-18
20006956 5.27% 8-Nov-18
20009898 5.43% 18-Sep-18
20009898 5.44% 18-Oct-18
20009898 5.55% 18-Nov-18
20009898 5.61% 18-Dec-18
20009898 5.00% 18-Jan-19
;

data want;
	if _N_=1 then do;
		declare hash h();
		h.definekey('Account');
		h.definedata('c');
		h.definedone();
	end;

	set have;
	
	rc=h.check();
 
    if rc ne 0 then do;
       c=1;
       h.add();
    end;
    if rc=0 then do;
       rc=h.find();	c=c+1;
       if c=2 then output;;
       rc=h.replace();
    end;
run;

Re: Selecting the 2nd occurance

Timbim — Wed, 28 Aug 2019 05:36:35 GMT

The 2nd occurrence for the account.

Re: Selecting the 2nd occurance

PeterClemmensen — Wed, 28 Aug 2019 05:37:49 GMT

Ok. Have you tried the code I posted above?

Re: Selecting the 2nd occurance

PeterClemmensen — Wed, 28 Aug 2019 05:51:09 GMT

Since you data is sorted by Account, you can also do this

data want;
	do _N_=1 by 1 until (last.Account);
		set have;
		by Account;
		if _N_=2 then output;
	end;
run;

With the hash object code though, the data does not have to be sorted by Account.

Re: Selecting the 2nd occurance

Kurt_Bremser — Wed, 28 Aug 2019 09:06:57 GMT

data want;
set have;
by account;
if first.account
then counter = 1;
else counter + 1;
if counter = 2;
drop counter;
run;

have needs to be sorted properly, of course.