topic Re: SAS Code to work with specific logic in SAS Programming

SAS Code to work with specific logic

KC_16 — Thu, 12 Dec 2019 11:09:05 GMT

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

Re: SAS Code to work with specific logic

s_lassen — Thu, 12 Dec 2019 11:29:53 GMT

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;

Re: SAS Code to work with specific logic

KC_16 — Thu, 12 Dec 2019 11:39:08 GMT

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?

Re: SAS Code to work with specific logic

s_lassen — Thu, 12 Dec 2019 12:22:38 GMT

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;

Re: SAS Code to work with specific logic

KC_16 — Thu, 12 Dec 2019 16:53:16 GMT

Thank you for your help, much appreciated.