Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting SAS macro codes in Alteryx

Zaid
8 - Asteroid

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

 

 

 

 

14 REPLIES 14
danilang
19 - Altair
19 - Altair

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

 

OutputConfig.png

 

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
8 - Asteroid

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
19 - Altair
19 - Altair

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

 

SplitMacro.png

 

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
8 - Asteroid

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
19 - Altair
19 - Altair

Glad to help

 

You can add more Macro outputs and more Selects as required

 

Dan

Labels