Do you really not have any other fields that help identify transactions?
Unfortunately not
Here is how I was able to generate the answer.
data DTTBL_00;
infile datalines delimiter='09'x dsd firstobs=1;
input Itm Qntty;
datalines;
1 45
1 -45
1 45
2 30
2 25
2 -30
2 25
;
run;
Data DTTBL_01;
Set DTTBL_00;
Cntr=_N_;
Run;
PROC SQL;
CREATE TABLE DTTBL_02 AS
SELECT t1.Itm,
t1.Qntty,
t1.Cntr
FROM WORK.DTTBL_01 t1
WHERE t1.Qntty < 0;
QUIT;
%macro BldGrph2(mcrItm, mcrQntty, mcrCntr);
%put =====> &mcrItm, &mcrQntty, &mcrCntr;
PROC SQL outobs=1;
CREATE TABLE DTTBL_03 AS
SELECT t1.Itm,
t1.Qntty,
t1.Cntr
FROM WORK.DTTBL_01 t1
WHERE t1.Qntty = abs(&mcrQntty);
QUIT;
PROC SQL;
delete from DTTBL_01
where Itm = (select Itm from DTTBL_03)
and Qntty = (select Qntty from DTTBL_03)
and Cntr = (select Cntr from DTTBL_03)
;
PROC SQL;
delete from DTTBL_01
where Itm = &mcrItm
and Qntty = &mcrQntty
and Cntr = &mcrCntr
;
%mend BldGrph2;
data _Null_;
set DTTBL_02;
Call EXECUTE('%BldGrph2('||Itm||','||Qntty||','||Cntr||')');
run;
Data RESULTS;
Set DTTBL_01;
run;
Do you need to keep the original OBS number? If OBS is a sequence counter, and the observations are in the order they occurred, then wouldn't OBS 3 be the one you want to keep instead of OBS 1 (OBS 2 is a reversal of OBS 1)?
If you don't need the OBS #, why not just sum the amounts by Number?
proc sql;
create table want as
select number, sum ( amount ) as sum_amt
from have
group by number
/* optional - exclude totals that equal zero */
having sum ( amount ) ^= 0
order by 1
;
quit;
That looks like a transactional data where you're reversing a transaction. There's usually some ID that tells you what type each transaction is and how to obtain the 'last' transaction for each transaction.
I get that - it looks like it is some type of transactional data on its face. But my question is why keep the OBS if it isn't needed? Just gettiing back to basics . . . simpler is better.
Now if the OP wants to keep OBS because it is important to further analysis, then fine. But, the data is not necessarily structured in such a way as to know whether subsequent events relate to prior events. We certainly don't have the business context to know. We are assuming based on limited information.
Further, in the example provided, the OP wants to keep OBS 1. Sequentially, however, OBS 2 appears to be a reversal of OBS 1. So, shouldn't OBS 3 be the row to keep if OBS is important?
My point was doing it in this manner is most likely not the best manner, and I was responding to the OP.
Given the provided information, your answer/comments are perfectly valid.
I missed that you were responding to the OP. Gotcha - just having a conversation here - hope I'm not coming across as adversarial or critical. I've been off forums for awhile, just back, and trying to re-engage.
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!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.