BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KC_16
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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;     

View solution in original post

4 REPLIES 4
s_lassen
Meteorite | Level 14

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
Fluorite | Level 6
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
Meteorite | Level 14

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
Fluorite | Level 6
Thank you for your help, much appreciated.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 714 views
  • 0 likes
  • 2 in conversation