Converting SAS macro codes in Alteryx

[Zaid]

Hi All,

I am converting SAS scripts into Alteryx workflow. I have successfully transformed the data and proc steps into the workflow. However, there are a few macros that is there. I would like to request some ideas on how to convert it in ALteryx and how to pass the values dynamically into the workflow. Based on the values being passed as arguments, new tables are getting created. I am writing the demo version of the code below. Any help/leads will be appreciated.

%MACRO Macroname  (x, y);

PROC SQL;
CREATE TABLE TABLENAME_&Y. AS
SELECT
DISTINCT *,
'TEXT ' || "&Y." || ' TO DISPLAY' AS ABC
FROM TABLEA A

Join TABLEB B
on COMPRESS(a.&X.) = COMPRESS(B.ID)

Now this is the macro and the following arguments is being passed.

%Macroname(P, Q)

%Macroname(E, F)

%Macroname(R, S)

So these values are being passed to the existing macro code and separate tables are being created.

Thanks

Zaid

[danilang]

Hi @Zaid 

Can you give us a little more detail about what you're trying to accomplish?

Do you want to be able able to reproduce the text of the macro?

The functionality, i.e. write an alteryx macro that creates the tables?

A macro where you pass the X and Y params and it calls the SAS macro with the params in the proper spot?

Dan

[Zaid]

@danilang the requirement is such that new tables are being created from the macro that i have written above based on the values that are being passed through the parameter.

So basically, depending upon whatever values that are being passed, the macro code runs and create tables dynamically. ALso we see that on join condition as well value is being passed. So the values are getting updated dynamically.

Thanks

Zaid

[Zaid]

Hi All,

Is there any way I can achieve the task. I am looking for a solution which will execute the same task repeatedly for the values that is passed.

Thanks

Zaid

[Zaid]

Hi All,

Still awaiting response guys on how to automate this section of the code in the workflow.

Thanks

Zaid

[Zaid]

Hi @danilang,

I will elaborate briefly what i am trying to achieve. I have converted the process in Alteryx manually. As in i had created 10 tables manually, which was being done through a macro. The thing is that I wanted to know how can i automate this process, where i can get the tables created dynamically. As in, these tables can be created through a macro by reading in the arguments from the macro, instead of me having to do this step n number of times.

%MACRO Macroname  (x, y);

PROC SQL;
CREATE TABLE TABLENAME_&Y. AS
SELECT
DISTINCT *,
'TEXT ' || "&Y." || ' TO DISPLAY' AS ABC
FROM TABLEA A

Join TABLEB B
on COMPRESS(a.&X.) = COMPRESS(B.ID)

Now this is the macro and the following arguments is being passed.

%Macroname(P, Q)

%Macroname(E, F)

%Macroname(R, S)

[danilang]

Hi @Zaid 

Are you referring to 2 different macros in your descriptions?  I believe that one of them is a SAS Macro and the other is an Alteryx macro, but I can't be sure.  Does the Alteryx macro call the SAS macro?  Can you please provide details of the Alteryx macro?  Posting the entire macro would be the best, but if you can't do that, then at least some screen shots of the inputs and what you're expecting as the output.

Dan

[Zaid]

Hi danilang,

The SAS macro that is pasted here needs to be converted into a relevant batch macro in Alteryx.

So whatever, the SAS macro is doing, i just want to convert it into Alteryx. So seeking the relevant approach to do it in Alteryx.  

[danilang]

hi @Zaid 

There is no simple way to execute generic SQL code directly from Alteryx.  The closest thing is to run a Pre- or Post-SQL query in an input or output tool. 

The way you should approach this is to build a workflow that generates the Create Table query as a string based on a list of your input params (P, Q), (E, F), (R, S).  Pass these complete SQL strings into a batch macro that modifies the SQL text of the Pre-SQL command

The solution to this article shows a way to dynamically run Pre- and Post-SQL commands by using a batch macro to modify the SQL text using the control parameter.  You should be able to modify this technique to run the Create table of your SQL.

Hope this helps

Dan

[Zaid]

Hi @danilang ,

Thanks for the reply. In another way to achieve the solution,I have created a batch macro doing the particular task and using that particular macro in another workflow to perform the joins dynamically. PLease find the screenshot of the macro.

Now the problem is I am getting a Union-ed output of all iterations of the batch macro in my workflow. Is there any way this macro will produce 'n' output nodes instead of giving me union of these n times.

Can you help me with this.

Thanks

Zaid