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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.