SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to split a column into rows dynamically in sas di?

Reply
Contributor
Posts: 23

how to split a column into rows dynamically in sas di?


we have a requirement like this.

src

src col1src col2src col3
aa1a123a234
bb1b123b234b345

tgt

tgt col1tgt col2tgt col3.1tgt col3.2
aa11a123
aa12a234
bb11b123
bb12b234
bb13b345

We need to split the src_col3 into multiple rows such that in each row, the tgt_col3.2 will have only 4 characters. The no. of rows that can result from a single row is dynamic as it is based on the data length of the src_col3. tgt_col3.1 is a sequnece no whcih denotes, for each tgt_col1 ie."a", how many rows are made up.

Can this be achieved using SAS DI with any transformation?

Super User
Posts: 5,429

Re: how to split a column into rows dynamically in sas di?

Looks like a job for a data step within a User Written Code transformation.

The code will be as dynamic as you write it, using the transformations dynamic macro variables as much as possible.

Data never sleeps
Contributor
Posts: 23

Re: how to split a column into rows dynamically in sas di?

so, cant we achieve this without going for an user written transformation?

Super User
Posts: 5,429

Re: how to split a column into rows dynamically in sas di?

Not really.

The standard transformation scenario could be:

  • On extract transform for each position interval that you can possibly have (depending on the maximum length of src_col3).
  • Append the results

So it wouldn't be very dynamic...

Data never sleeps
Super User
Posts: 5,429

Re: how to split a column into rows dynamically in sas di?

Another option would be to:

  • Use Extract to split the src_col3 into all necessary columns (depending on the maximum length of src_col3)
  • Try to master the Transpose transformation to generate for each new src_col3 column.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 690 views
  • 0 likes
  • 2 in conversation