topic Re: Running a program over multiple datasets in SAS Programming

Running a program over multiple datasets

ICL1986 — Tue, 11 Jun 2019 07:19:02 GMT

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?

Re: Running a program over multiple datasets

SASKiwi — Tue, 11 Jun 2019 07:50:12 GMT

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;

Re: Running a program over multiple datasets

ICL1986 — Tue, 11 Jun 2019 08:23:01 GMT

That's perfect thank you

Re: Running a program over multiple datasets

edasdfasdfasdfa — Sat, 06 Feb 2021 02:23:24 GMT

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?