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

[Sandhyaa]

we have a requirement like this.

src

src col1	src col2	src col3
a	a1	a123a234
b	b1	b123b234b345

tgt

tgt col1	tgt col2	tgt col3.1	tgt col3.2
a	a1	1	a123
a	a1	2	a234
b	b1	1	b123
b	b1	2	b234
b	b1	3	b345

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?

[LinusH]

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.

[Sandhyaa]

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

[LinusH]

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...

[LinusH]

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.