BookmarkSubscribeRSS Feed
Reeza
Super User

Do you really not have any other fields that help identify transactions?

Alex_mcc
Calcite | Level 5

Unfortunately not

Bryan
Obsidian | Level 7

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;

Fugue
Quartz | Level 8

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;

Reeza
Super User

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.

Fugue
Quartz | Level 8

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?

Reeza
Super User

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.

Fugue
Quartz | Level 8

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. Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

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.

Discussion stats
  • 22 replies
  • 1661 views
  • 2 likes
  • 5 in conversation