topic Split concatenated field into multiple columns using Dataflux 2.8 in SAS Data Management

Split concatenated field into multiple columns using Dataflux 2.8

beamer108 — Fri, 28 May 2021 17:06:58 GMT

I have an Address field that was concatenated to include Addr1, Addr2, Addr3, City, State, Zip etc. In dataflux I need to take that Address field and create separate columns for each piece of the address.

Example:

The columns in yellow are what I need created using dataflux - either eel or is there a specific node that would do the trick.

Thanks in advance

Re: Split concatenated field into multiple columns using Dataflux 2.8

ballardw — Sat, 29 May 2021 19:04:37 GMT

First thing: I haven't any experience with Dataflux.

I think that any simple "node" or such is going to have issues because of varying data in your concatenated variable. Even your example does not show three "street address" bits, only two and this if you count a PO Box as a street address. Many things dealing with addresses will not use "street address" because so many elements that may appear there aren't really "street": PO BOX, or APO, Rural route, Floor/Suite/Apartment/Office at such.

I could probably manage this in a data step in base SAS programming assuming 1) all of the values are indeed comma separated 2) all of them have a city, state and zip code.

Re: Split concatenated field into multiple columns using Dataflux 2.8

audrey — Mon, 31 May 2021 07:50:09 GMT

Hi,

In Data Management Studio, you can use the Parsing node (under Quality) with the Address (Full) definition. This requires a Quality Knowledge Base.

https://support.sas.com/downloads/browse.htm?fil=&cat=540

Hope this helps.

Audrey