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;
Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.
Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.
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.