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?
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.
so, cant we achieve this without going for an user written transformation?
Not really.
The standard transformation scenario could be:
So it wouldn't be very dynamic...
Another option would be to:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.