BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Divya8
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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.

View solution in original post

15 REPLIES 15
Divya8
Calcite | Level 5
 
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Calcite | Level 5
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
Calcite | Level 5
Please help me on this
novinosrin
Tourmaline | Level 20

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
PROC Star

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

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
Meteorite | Level 14

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
Calcite | Level 5
Thank you. It’s working. I tried yours and @Ksharp suggestions both are working as exepected
Divya8
Calcite | Level 5
@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
Tourmaline | Level 20

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
Calcite | Level 5
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
Calcite | Level 5
Please help on this

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 15 replies
  • 1861 views
  • 0 likes
  • 5 in conversation