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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.