Hello,
I have the following final table (every day the table contents change):
Bank_ID | Branch_ID | Account_Number |
20 | 1 | 1234567 |
20 | 112 | 3224334 |
20 | 12 | 1234567 |
20 | 34 | 2344523 |
20 | 22 | 1234567 |
I want to make a loop that will run as the number of records in the table.
Each run will define all the variables in the table as global variables with the values of the relevant record.
According to the example, the loop will have 5 runs.
Example of a first run:
Bank_ID=20
Branch_ID=1
Account_Number=1234567
Example of a second run:
Bank_ID=20
Branch_ID=112
Account_Number=3224334
At each run, I will take the value of the global variables for sending an email.
I would appreciate your help.
Repeating what others already proposed and then some sample steps to demonstrate how to get there.
What you need is a SAS macro which you then can call out of a SAS data step where you pass in your SAS variables as parameters to the SAS macro for every row in your table.
Step 1: Create static and fully tested code for sending an email. Below just some sample code that will print something.
data _null_;
file print;
put
"From: freetalk@gmail.com" /
"To: abc@gmail.com" /
"Subject: Closing <Account_Number> - <Branch_ID>" /
"Email content:"
"Cancel any instructions in the account <Account_Number> - <Branch_ID> -<Bank_ID>" /
;
run;
Step 2: Once the static code is working make it dynamic via a SAS macro. You basically need to create a parameter for all the bits in your code that will need to take on different values.
%macro mymail(
from=freetalk@gmail.com,
to=abc@gmail.com,
acct_no=,
branch_id=,
bank_id=
);
data _null_;
file print;
put
"From: &from" /
"To: &to" /
"Subject: Closing &acct_no - &branch_id" /
"Email content:"
"Cancel any instructions in the account &acct_no - &branch_id - &bank_id" /
;
run;
%mend;
And then test the macro.
%mymail(
to=abc@gmail.com,
acct_no=ACCT0001,
branch_id=BR025,
bank_id=BANK001
);
Step 3: If above is working then you're ready to also generate the macro calls.
/* create sample data */
data sample;
infile datalines truncover dlm='|' dsd;
input to_addr :$40. Account_Number :$20. Branch_ID :$20. Bank_ID :$20.;
datalines;
email_1@test.com|ACCT0001|BR001|BANK001
email_99@test.com|ACCT0099|BR099|BANK099
;
/* call macro once per observation in sample table */
data _null_;
set sample;
length cmd $500;
cmd=cats( '%mymail(to=',to_addr,',acct_no=',Account_Number,',branch_id=',Branch_ID,',bank_id=',Bank_ID,')' );
call execute(cmd);
run;
And here you go:
I don't think global MACRO variables are needed.
How are you planning to create the email?
If you are using a data step to create the email then just leave the data in the dataset variables and use the dataset directly in the data step that writes the email. without needing to convert your dataset variables into text strings to store into macro variables. With email directives you should be able to write all of the emails in one data step. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/n0ig2krarrz6vtn1aw9zzvtez4qo...
Otherwise create a macro with input parameters that match the variables in your dataset. Then use a data step to generate one call to the macro for each observation in the dataset.
%macro email
(bank_id
,branch_id
,account_number
);
.... details of how to send an email for this account ...
%mend;
filename code temp;
data _null_;
set have;
file code;
put '%email(' bank_id= ',' branch_id= ',' account_number= ')';
run;
%include code / source2;
So the text file generated by the data _null_ step and executed by the %INCLUDE statement will look like:
%email(Bank_ID=20 ,Branch_ID=1 ,Account_Number=1234567)
%email(Bank_ID=20 ,Branch_ID=112 ,Account_Number=3224334)
Look into CALL EXECUTE instead IMO. Creating 3*# of loop of macro variables + 1 seems cumbersome and it's a PITA to debug.
EDIT: here are some references that may be helpful for you.
Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md
Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...
I'm sorry but I still do not know how to start.
I will simplify my request, I have a SAS table that I want to define 3 global variables whose value varies in each record.
The amount of records and their value changes daily.
In the case of the example I would like to use the 3 variable values in each round to sending an email according to the following example:
From: freetalk@gmail.com
To: abc@gmail.com
Subject: Closing <Account_Number> - <Branch_ID>
Email content:
Cancel any instructions in the account <Account_Number> - <Branch_ID> -<Bank_ID>
Will send 5 emails.
Thanks.
Please show to code you want to run to send the first email.
Then you can think about how to generate that code from your data.
Repeating what others already proposed and then some sample steps to demonstrate how to get there.
What you need is a SAS macro which you then can call out of a SAS data step where you pass in your SAS variables as parameters to the SAS macro for every row in your table.
Step 1: Create static and fully tested code for sending an email. Below just some sample code that will print something.
data _null_;
file print;
put
"From: freetalk@gmail.com" /
"To: abc@gmail.com" /
"Subject: Closing <Account_Number> - <Branch_ID>" /
"Email content:"
"Cancel any instructions in the account <Account_Number> - <Branch_ID> -<Bank_ID>" /
;
run;
Step 2: Once the static code is working make it dynamic via a SAS macro. You basically need to create a parameter for all the bits in your code that will need to take on different values.
%macro mymail(
from=freetalk@gmail.com,
to=abc@gmail.com,
acct_no=,
branch_id=,
bank_id=
);
data _null_;
file print;
put
"From: &from" /
"To: &to" /
"Subject: Closing &acct_no - &branch_id" /
"Email content:"
"Cancel any instructions in the account &acct_no - &branch_id - &bank_id" /
;
run;
%mend;
And then test the macro.
%mymail(
to=abc@gmail.com,
acct_no=ACCT0001,
branch_id=BR025,
bank_id=BANK001
);
Step 3: If above is working then you're ready to also generate the macro calls.
/* create sample data */
data sample;
infile datalines truncover dlm='|' dsd;
input to_addr :$40. Account_Number :$20. Branch_ID :$20. Bank_ID :$20.;
datalines;
email_1@test.com|ACCT0001|BR001|BANK001
email_99@test.com|ACCT0099|BR099|BANK099
;
/* call macro once per observation in sample table */
data _null_;
set sample;
length cmd $500;
cmd=cats( '%mymail(to=',to_addr,',acct_no=',Account_Number,',branch_id=',Branch_ID,',bank_id=',Bank_ID,')' );
call execute(cmd);
run;
And here you go:
Thank you very much, your solution is excellent.
I will need your help again , if the fields: Branch_ID and Account_Number, and to_addr are the same in the table
data sample;
infile datalines truncover dlm='|' dsd;
input to_addr :$40. Account_Number :$20. Branch_ID :$20. Bank_ID :$20.;
datalines;
email_99@test.com|ACCT0001|BR001|BANK001
email_99@test.com|ACCT0001|BR001|BANK231
email_1@test.com|ACCT0099|BR099|BANK099
;
Will send 2 emails, the first will look like this:
From: freetalk@gmail.com
To: email_99@test.com
Subject: Closing ACCT0001 - BR001
Email content:
Cancel any instructions in the account:
ACCT0001 - BR001 -BANK001
ACCT0001 - BR001 -BANK231
2 records will enter the same email only with a different Bank_ID field.
It is possible?
Thanks.
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.