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
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;
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;
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;
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!
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.