How to create global variables in a loop

[shlomiohana]

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.

[Tom]

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)

[Reeza]

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...

[shlomiohana]

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.

[Tom]

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.

[Patrick · Accepted solution]

@shlomiohana 

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:

[shlomiohana]

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.