Conditionally executing multiple macro

[Santt0sh]

Hi All,

I trying to send emails to different users, i have written two SAS macros let's say Macro A, and Macro B.

for certain reasons i am not able to share the code here as i am working on secured network, but I will try my best to explain what the two macro do.

Macro A.: Macro creates a table for each department (i.e. Dept A, B ,C) the macro creates the Subset of data from the  Repository for the mentioned period. which will extract the details of the Sales, location, Product and user details like Name of the user, email id.

Macro B: Macro creates the intermediate tables which will have details of the user, and his Sales data viz, Sales for each location summaries the data for each salesperson, for each location he is responsible. and emails the information to him and his manager.

when i am running the the SAS Macro for Dept A, Dept B and Dept C individually i am able to get the desired results.

for ex. 

Dept A has 2 sales done by Salesperson XYZ, the Salesperson and his manager is receiving emails for the 2 sales made by him for the Week.

Similarly Dept B has 1 Sales done by Salesperson ABC the  Salesperson and his manager is receiving emails for the 1 sale made by him for the Week.

Similarly Dept c has 10 Sales done by Salesperson UVW the Salesperson and his manager is receiving emails for the 10 sales made by him for the Week.

Only 3 emails sent for each sale. for the week.

I am able to achieve this by running both Macro and Macro B for each Dept, But when i am running it for all the 3 Dept(A,B,C) ii am getting as many as 12 emails. instead of 3 emails.

 Execution: Macro execution order

Macro A; to create the Subset for each dept, 

and Macro B is called within Macro A, in order to send emails to the Salesperson who has made sales.

Could you please suggest me how to Run for all the departments and send emails, as this will run in a Batch in prod environment?

Regards,

[Reeza · Accepted solution]

1. Make fake data

2. Modify your code to work with the fake data

3. Post it here so we can help with more information. 

It's hard to say what's wrong with your code if we can't see it.

---

@Santt0sh wrote:

Hi All,

I trying to send emails to different users, i have written two SAS macros let's say Macro A, and Macro B.

for certain reasons i am not able to share the code here as i am working on secured network, but I will try my best to explain what the two macro do.

Macro A.: Macro creates a table for each department (i.e. Dept A, B ,C) the macro creates the Subset of data from the  Repository for the mentioned period. which will extract the details of the Sales, location, Product and user details like Name of the user, email id.

Macro B: Macro creates the intermediate tables which will have details of the user, and his Sales data viz, Sales for each location summaries the data for each salesperson, for each location he is responsible. and emails the information to him and his manager.

when i am running the the SAS Macro for Dept A, Dept B and Dept C individually i am able to get the desired results.

for ex. 

Dept A has 2 sales done by Salesperson XYZ, the Salesperson and his manager is receiving emails for the 2 sales made by him for the Week.

Similarly Dept B has 1 Sales done by Salesperson ABC the  Salesperson and his manager is receiving emails for the 1 sale made by him for the Week.

Similarly Dept c has 10 Sales done by Salesperson UVW the Salesperson and his manager is receiving emails for the 10 sales made by him for the Week.

Only 3 emails sent for each sale. for the week.

I am able to achieve this by running both Macro and Macro B for each Dept, But when i am running it for all the 3 Dept(A,B,C) ii am getting as many as 12 emails. instead of 3 emails.

 Execution: Macro execution order

Macro A; to create the Subset for each dept, 

and Macro B is called within Macro A, in order to send emails to the Salesperson who has made sales.

Could you please suggest me how to Run for all the departments and send emails, as this will run in a Batch in prod environment?

Regards,

---

[Santt0sh]

Sample Macro:

libname mydblib oracle user=testuser password=testpass path=hrdept_002;

%macro A(dsn=,dept=);

data &dsn.;

set mylibdb. sales_rep;

where department = &dept and salesdate = today() - intnx('day',today(),-7);

run;

< Summarisation of the sales data>

    %B(Sales_A,abc,));

%mend;

%macro B(dsn,slesp);

Creation of Macro variables and to Send emails to the Salesperson and his Manager.

%mend;

%A(dept = Sales_A, Dept=A);

[Reeza]

Sorry, that's not enough information for me to help, I can't run it or see where the issues may be.

Hopefully someone else will be able to help you shortly. 

---

@Santt0sh wrote:

Sample Macro:

libname mydblib oracle user=testuser password=testpass path=hrdept_002;

%macro A(dsn=,dept=);

data &dsn.;

set mylibdb. sales_rep;

where department = &dept and salesdate = today() - intnx('day',today(),-7);

run;

< Summarisation of the sales data>

    %B(Sales_A,abc,));

%mend;

%macro B(dsn,slesp);

Creation of Macro variables and to Send emails to the Salesperson and his Manager.

%mend;

%A(dept = Sales_A, Dept=A);

---

[Santt0sh]

Hi Reeza,

Thank you for you quick reply. I will try and recreate the issue and post the Code here.

Thanks

[Astounding]

In the code that you are not showing ...

Are you using SQL to join data sets?

Do you understand what a Cartesian product is?

[Santt0sh]

Hi,
Yes I am using proc sql to join tables, and I am using left join the resulting table is as expected. And I don’t see any Cartesian product.

[Astounding]

Left join will handle mismatches, but it still leaves open the possibility of a Cartesian product.  Consider this data:

Data Set A

ID  A_Value

1    A1

1    A2

Data Set B

ID  B_Value

1    B1

1    B2

1    B3

You can join these any way you want (left, right, inner) matching on ID.  You will always get 6 observations.