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

[wonka1234]

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_Name	n_Value
App BOR ID	LSTEVENS4
Role	Role=88853369 - CSS CSR
appUserName	Louis Stevens

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

Thanks.

[binu_acs]

@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]

 @binu_acs  thanks for this!

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

[Ben_H]

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]

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.

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

[binu_acs]

@wonka1234 similar to @Robin_McIntosh solution 

[Robin_McIntosh]

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