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 

It looks like you're actually retrieving all the records and doing the join in the macro.  Very good. 

Is there a field in your output that holds the Table name?  i.e. records 1 to 10 correspond to TableName_Q,  11-23 to TableName_F, etc.  If not, add one.  With this information, you can write the records to the database with an Output Tool configured like this

It will create new tables based on the information in the Table name field.  Make sure that that your records are sorted by table name and then key.   

Dan

[Zaid]

Hey @danilang ,

thanks for the reply. The objective is not to create tables, but different datasets. I do not want to store it anywhere. The data sets created are going to be again used for further separate calculations. So. a unioned output is not what i want, I am looking for just separate output nodes.

For better understanding, I have uploaded the snip of the workflow. 

Thanks

Zaid

[danilang · Accepted solution]

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

[Zaid]

Thanks a lot @danilang. This will work but the only pain point is i have to create n number of macro outputs. This will work wonders, if less value is passed.

Regards,

Zaid

[danilang]

Glad to help

You can add more Macro outputs and more Selects as required

Dan