Hi, I am looking to run a simple program over a large number of datasets that have been created from a banking system extract.
Essentially we want to extract the transactions for different accounts and then calculate balances for each account. I know how to extract the data by account, and have created some dummy data to add the balance once I've extracted the data by account as below (dummy data attached):
data want;
set OUT_12345;
retain Balance;
if _N_=1 then Balance=Credit-Debit;
else Balance=Balance+Credit-Debit;
run;
What I don't know how to do is run the above code over all the datasets (which would be called OUT_12345, OUT_12346, OUT_12347 etc.), I've looked at other solutions but my lack of experience with macros means I'm totally stuck at the moment. Can anyone help?
You don't need macro code to do what you want. It would be a whole lot easier to extract one dataset containing all accounts transactions sorted by account ID or as I've done here combine your separate datasets in the one step
data want;
set OUT_:; * This will read all your separate datasets in;
by account_id;
retain Balance;
if first.account_id then Balance=Credit-Debit;
else Balance=Balance+Credit-Debit;
run;
You don't need macro code to do what you want. It would be a whole lot easier to extract one dataset containing all accounts transactions sorted by account ID or as I've done here combine your separate datasets in the one step
data want;
set OUT_:; * This will read all your separate datasets in;
by account_id;
retain Balance;
if first.account_id then Balance=Credit-Debit;
else Balance=Balance+Credit-Debit;
run;
That's perfect thank you
Sorry, late question(s).
To get the proper results, would you not need to sort before combing all the data-sets? Otherwise, is the BY statement really going to get the result you want?
And also, how can just combine like that with set when it wasn't imported?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: