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.
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;
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;
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!
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.
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;
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.
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?
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!
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.