Find the observation in a group

[Divya8]

I need to select 7400 and 7470 transaction in each group. Write record to the output when group A has 7400 and 7470 transaction. For group C first record have 7400 transaction but it doesn’t have 7470 transaction in susequent record. So I don’t need to consider that. For group C in need to consider only last 2 rows.

Input
Group sequence transaction
A. 100. 1200
A. 101 7400
A. 102 1000
A. 103 7470
B. 201. 7400
B. 202. 4044
B. 203. 4600
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 301. 7400
C. 302. 1000
C. 303. 7400
C. 304. 7470

Output should look like
Group sequence transaction
A. 101. 7400
A. 103. 7470
B. 201. 7400
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 303. 7400
C. 304. 7470

[novinosrin]

Little cheeky answer. Please test and let me know 🙂

data have;
input Group $ sequence transaction;
cards;
A. 100. 1200
A. 101 7400
A. 102 1000
A. 103 7470
B. 201. 7400
B. 202. 4044
B. 203. 4600
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 301. 7400
C. 302. 1000
C. 303. 7400
C. 304. 7470
;

data w;
set have;
by Group transaction notsorted;
where transaction  in (7400,7470);
if last.transaction;
run;

[novinosrin]

Or 

data have;
input Group $ sequence transaction;
cards;
A. 100. 1200
A. 101 7400
A. 102 1000
A. 103 7470
B. 201. 7400
B. 202. 4044
B. 203. 4600
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 301. 7400
C. 302. 1000
C. 303. 7400
C. 304. 7470
;

data want;
set have;
by Group transaction notsorted;
where transaction  in (7400,7470);
if (last.transaction and transaction=7400) or (first.transaction and transaction=7470);
run;

[Divya8]

I tried both options. It’s working partially. It gives

A. 101. 7400
A. 103. 7470
B. 201. 7400
B. 204. 7470
B. 205. 7400
B. 206. 7470
C 301 7400 - this entry should not come.
C. 303. 7400
C. 304. 7470

The above entry not having 7470 transaction. So it’s should not consider

[Divya8]

Please help me on this

[novinosrin]

HI @Divya8  It's nice to note that you have more comprehensive solutions from @Ksharp  and @Astounding than my lazy one. Nevertheless, to your comment that i marked in red. I ran a test yet again with your sample to see what i get

A. 101. 7400
A. 103. 7470
B. 201. 7400
B. 204. 7470
B. 205. 7400
B. 206. 7470
C 301 7400 - this entry should not come.
C. 303. 7400
C. 304. 7470

TEST:

/*Creatinbg sample HAVE*/
data have;
input Group $ sequence transaction;
cards;
A. 100. 1200
A. 101 7400
A. 102 1000
A. 103 7470
B. 201. 7400
B. 202. 4044
B. 203. 4600
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 301. 7400
C. 302. 1000
C. 303. 7400
C. 304. 7470
;
data want;
set have;
by Group transaction notsorted;
where transaction  in (7400,7470);
if (last.transaction and transaction=7400) or (first.transaction and transaction=7470);
run;

RESULTS:

The SAS System

Group	sequence	transaction
A.	101	7400
A.	103	7470
B.	201	7400
B.	204	7470
B.	205	7400
B.	206	7470
C.	303	7400
C.	304	7470

How did you get C 301 7400? Can you please check the above results?

Best Regards!

[Astounding]

It looks like you need to account for a few possibilities:  multiple 7400 records, a 7400 without a matching 7470 record, a 7470 record without a matching 7400 record.  That will require two passes through the data.  Here is one approach:

data want;
   recnum=0;
   _7400_=0;
   _7470_=0;
   do until (last.group or transaction=7470);
      set have;
      by group;
      recnum + 1;
      if transaction=7400 then _7400_ = recnum;
      if transaction=7470 then _7470_ = recnum;
   end;
   recnum=0;
   do until (last.group or transaction=7470);
      set have;
      by group;
      recnum + 1;
      if (0 < _7400_ < _7470_) and
      (recnum = _7400_ or recnum=_7470_) then output;
   end;
   drop recnum _7400_ _7470_;
run;
   

It's untested code, so might need a tweak or two.

The top DO loop locates observations containing 7400 or 7470.  The bottom DO loop reads the exact same observations, and outputs, depending on the results of the top DO loop.

[Ksharp]

So you want match 7400 and 7470 one to one strictly ?

data have;
input Group $ sequence transaction;
cards;
A. 100. 1200
A. 101 7400
A. 102 1000
A. 103 7470
B. 201. 7400
B. 202. 4044
B. 203. 4600
B. 204. 7470
B. 205. 7400
B. 206. 7470
C. 301. 7400
C. 302. 1000
C. 303. 7400
C. 304. 7470
;
data have;
 set have;
 by group;
 if first.group or transaction=7400 then n+1;
data _7400 _7470;
 set have;
 if transaction=7400 then output _7400;
 if transaction=7470 then output _7470;
run;
data temp;
 ina=0;inb=0;
 merge _7400(in=ina) _7470(in=inb 
 rename=(sequence=se transaction=tr));
 by group n;
 if ina and inb;
run;
data want;
 set temp;
 output;
 sequence=se; transaction=tr;output;
 drop se tr n;
run;

[s_lassen · Accepted solution]

A small change to the elegant solution suggested by @novinosrin :

Data want;
set have;
by Group transaction notsorted;
where transaction  in (7400,7470);
if (last.transaction and transaction=7400 and not last.group) or (first.transaction and transaction=7470 and not first.group);
run;

This way, you will not get an output if the first transaction in a group is 7470, or the last transaction in a group is 7400.

[Divya8]

Thank you. It’s working. I tried yours and @Ksharp suggestions both are working as exepected

[Divya8]

@novinosrin recently I come across the below scenario where the sas logic not giving me the expected output.

I have added another 4320 transaction along with 7470 transaction to check. Below is the code I used

if (last.transaction and transaction=7400 and not last.group) or (first.transaction and transaction=7470 or transaction = ‘4320’ and not first.group);
run
D 500 4060
D 401 7400
D 403 4320
D 404 7400
D 405 7415
D 406 7420
D 407 7400
D 408 7470

Along with earlier mentioned cases i need to get below data for D group

D 401 7400
D 408 7470

In actual getting

D 401 7400
D 403 4320
D 407 7400
D 408 7470

Please help me on this

[novinosrin]

Hello @Divya8   I believe Soren's answer works well. 

Also  how does transaction 4320 matter when you have already filtered to subset only 7400,7470. Do you mean there's a change in requirement to what is stated as the original objective? 

[Divya8]

Yes. There is a change. I need to consider first occurrence of 7400 in a group and the last occurrence of 7470/4320. I have altered the code as below

Data want;
set have;
by Group transaction notsorted;
where transaction in (7400,7470,4320);

if (last.transaction and transaction=7400 and not last.group) or (first.transaction and transaction=7470 or transaction = 4320 and not first.group);

The code is working in for case A,B,C, E but not D

Input
Group sequence transaction
A 100 1200 jan10
A 101 7400 jan10
A. 102 1000 jan10
A 103 7470 jan10
B 201 7400 jan10
B 202 4044 jan10
B. 203. 4600 jan10
B. 204. 7470 jan10
B. 205. 7400 jan10
B. 206. 7470 jan10
C. 301. 7400 jan10
C. 302. 1000 jan10
C. 303. 7400 jan10
C. 304. 7470 jan10
D 500 4060 jan10
D 401 7400 jan10
D 403 4320 jan10
D 404 7400 jan10
D 405 7415 jan10
D 406 7420 jan10
D 407 7400 jan10
D 408 7470 jan10
E 500 7400 jan10
E 501 1000 jan10
E 502 4320 jan10
E 503 7400 jan11
E 504 7470 jan11


Output should look like
Group sequence transaction
A. 101. 7400 jan10
A. 103. 7470 jan10
B. 201. 7400 jan10
B. 204. 7470 jan10
B. 205. 7400 jan10
B. 206. 7470 jan10
C. 303. 7400 jan10
C. 304. 7470 jan10
D 401 7400 jan10
D 408 7470 jan10
E 500 7400 jan10
E 502 4320 jan10
E 503 7400 jan11
E 504 7470 jan11

[Divya8]

Please help on this