In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

recreating a few SAS functions (COMPRESS, CAT, STRIP, TRANWRD) in alteryx

wonka1234
10 - Fireball

I am trying to figure out what this does in sas so I can replicate it in alteryx.

 

COMPRESS(CAT(STRIP(n_Attribute_Name),TRANWRD(STRIP(n_Value),STRIP(n_Attribute_Name),"")),"_","KAD")

 

Here is sample data:

 

n_Attribute_Namen_Value
App BOR IDLSTEVENS4
RoleRole=88853369 - CSS CSR
appUserNameLouis Stevens

 

 

Is there a way to replicate the functionality above in alteryx? 

 

Thanks.

6 REPLIES 6
binuacs
21 - Polaris

@wonka1234  Here is what I found in the web

 

STRIP - Trim()

TRANWRD - Replace()

CAT - Concatenate - eg: [Field1]+[Field2]

COMPRESS - RegexReplace   you can refer the URL for the options 'KAD' (How To Use Compress Function In SAS? - 9TO5SAS)

 

wonka1234
10 - Fireball

 @binuacs  thanks for this!

I am having trouble putting this into a formula, could you help ?

Ben_H
11 - Bolide

Hi @wonka1234,

 

Do you have an example output from SAS?

 

It looks like it's doing this -

 

REGEX_Replace(trim([n_Attribute_Name])+replace(trim([n_Value]),trim([n_Attribute_Name]),""),'\d*[A-Za-z]*','$1')

 

However, all this seems to do is return the punctuation and spaces.

 

Regards,

 

Ben

Robin_McIntosh
11 - Bolide

Based on the sample data and SAS expression above, the output would be this (new_field).

Within the TRANWRD function, the intent is to keep (modifier K) any underscores "_" and all alphanumeric digits (modifiers AD).

I added lines with underscore to show the SAS result of COMPRESS.

 

Robin_McIntosh_2-1656337290557.png

 

Here's a workflow with two routes to get the same result as SAS.

Robin_McIntosh_3-1656337324308.png

 

 

binuacs
21 - Polaris

@wonka1234 similar to @Robin_McIntosh solution 

binuacs_0-1656369975919.png

 

Robin_McIntosh
11 - Bolide

@wonka1234 - Does this answer your question/solve your issue?

Labels
Top Solution Authors