SAS Code to work with specific logic

[KC_16]

Hi,

I need some code if possible please to only return the data whereby the logic below is satisfied, any idea how I would write this code please?

Where we have a Duplicate 'Cust_No' 

Where 'TransAmount' has changed (one record could show £10, the next record could show £20) 

and 'TransID' has changed (one record could show 345, the next record could show 346). 

Only looking at records that have change since 1st April 2019 ('Date_In')

Thanks

[s_lassen]

Sounds like you have a large transaction dataset from which you only want part.

So, first sort the part you want by Cust_No and TransID (or a date variable?):

proc sort data=<large transaction table> out=temptrans;
  where Date_In>='01APR2019'd;
  by Cust_No TransID;
run;

Next, split the table in first and following records:

data temptrans1 temptrans2(rename=(TransID=nextTransID TransAmount=nextTransAmount));
  set temptrans;
  by Cust_No;
  if first.Cust_No then do;
    if not last.Cust_no then output temptrans1;
    end;
  else if last.Cust_No then
    output temptrans2;
  else do;
    output temptrans1 temptrans2;
run;     

Finally, merge the two parts and check for differences:

data want;
  merge temptrans1 temptrans2;
  by Cust_No;
  if TransID^=nextTransID and TransAmount^=nextTransAmount;
run;

[KC_16]

Hi S_Lassen,

Thanks for your help, I have tried running the second part of the code but I get an error message:

ERROR: There was 1 unclosed DO block

Any idea what I need to do to resolve this please?

[s_lassen · Accepted solution]

Sorry, put a DO too many in. That is what happens when you do not supply sample data to test on. The second data step should have been

data temptrans1 temptrans2(rename=(TransID=nextTransID TransAmount=nextTransAmount));
  set temptrans;
  by Cust_No;
  if first.Cust_No then do;
    if not last.Cust_no then output temptrans1;
    end;
  else if last.Cust_No then
    output temptrans2;
  else
    output temptrans1 temptrans2;
run;     

[KC_16]

Thank you for your help, much appreciated.