topic how to split a column into rows dynamically in sas di? in SAS Data Management

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

Sandhyaa — Wed, 16 Jan 2013 11:31:02 GMT

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?

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

LinusH — Wed, 16 Jan 2013 11:35:51 GMT

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.

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

Sandhyaa — Wed, 16 Jan 2013 12:23:02 GMT

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

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

LinusH — Wed, 16 Jan 2013 15:00:21 GMT

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

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

LinusH — Wed, 16 Jan 2013 15:04:01 GMT

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.