Converting SAS macro codes in Alteryx

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

Workflow

Macros

Transformation

Dynamic Processing

Accepted answers

danilang

Hi @Zaid

As far as I know, there is no way to dynamically add outputs to a macro. The best I can come up with is something like this

The Multi-row tool assigns records to the various outputs by tracking when the [Group] field changes. When it does the [OutputGroup] field is incremented by 1. The Select tools just route the records to corresponding output. You could have the Select tools directly check the [Group], but then every time the inputs change, you'd have to modify the Select criteria. This way the 1st group is routed to the 1st output, 2nd go to the 2nd, etc.

Dan

Main.yxzp

All comments

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280