How to create multiple rows from one field

[Suminder]

Hi,

I have following data set

Column - A

ac,dc,gh,th,kl

a,t,hj,tyu

xy,hgt,g125,ujy

How can I move it to 

Column B

ac

dc

gh

th

kl

a

thj

tyu

.

.

.

I am using SAS DI, would appreciate if I could get a function for this.

[Shmuel]

There are several ways to do what you want.

1) 

data have;
    input a $20.;
datalines;
ac,dc,gh,th,kl
a,t,hj,tyu
xy,hgt,g125,ujy
;
run

data want;
 set have;
      length b $3;
       i=1;
       do until (b=' ');
            b=scan(a,i,',');
            if b not missing then output;
            i+1;
      end;
run; 

2) Read data lines with delimiter=',' thus having 4 variables in step-1.

    Then run proc transpose to convert wide form to long form, dropping _name_ column. 

[Suminder]

Hi, Shmuel

Thanks for the reply...I would clear out the question more...

I have a .txt file that I have to move into SQL database. This text file have five fields and I receive this file daily with around 5k records in it e.g.

Field1             Field2           Field3                 Field4           Field5

id1                  name1          age1                  DOB1            chr1,chr2,chr3 

id2                   name2           age2                DOB2            chr4,chr5,chr6

I am using SAS DI for ETL,

The output should be as follows

Field1             Field2           Field3                 Field4           Field5

id1                  name1          age1                  DOB1            chr1 

id1                  name1          age1                  DOB1            chr2 

id1                  name1          age1                  DOB1            chr3 

id2                   name2           age2                DOB2            chr4

id2                   name2           age2                DOB2            chr5

id2                   name2           age2                DOB2            chr6

So, please base the solution based on above scenario....would appreciate if you could help me based on SAS DI environment as we use transformations(eg. File Reader, Extract etc.) for ETL and we do not use BASE SAS.

[Suminder]

I saw there is a TRANSPOSE transformation.. How do I use it...it is very confusing....an you guys help me with this?

[Tom]

Doing this in SQL is a big pain. Doing it in DI might be an even bigger pain. 

Why not try it as multiple pulls, one for each possible entry.

So to get the first entry

select Field1
     , Field2
     , Field3
     , Field4
     , scan(Field5,1,',','mq') as Field5
from have
where countw(field5,',','mq') >= 1

Then either run it again but change 1 to 2 to get the second entries. Repeat for as many possible values that could appear.

Either run them separatedly or UNION them into one big query.

[JackHamilton]

For an approach that lets you run the SQL code in Tom's SQL, but without the need for a separate run for each entry, see my ancient paper 

https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/046-31.pdf

Basically, you would create a utility table with as many rows as you have potential entries (so 5 entries means you have rows in the new table with the values 1, 2, 3, 4, 5) and add that table to the join, using the number from the utility table in the scan function.  You'll get a warning about a cartesian join.  If your "have" table (or a view created from it) has a column containing the number of output records you want, you can join on that number and make the process potentially faster.

[Patrick]

@Suminder 

It's also with DIS not that hard. You just can't use an out-of-the-box transformation which does everything for you but you need to implement user written code.

The DIS way is to implement such user written code directly within the External File object as documented here:

https://go.documentation.sas.com/?docsetId=etlug&docsetTarget=p1h7xnr4n6dmown11ceyi9nmpq7p.htm&docse...  

You then can use this External File object as you normally would (as input for a file reader) and then load the extracted table into target via whatever DIS transform is appropriate (like the Table Loader).

Below SAS code demonstrating how you can read your data into the desired target structure - so code close to what you will need for your External File.

/* create the sample external file */
filename extfile temp;
data _null_;
  file extfile;
  put
    'Field1|Field2|Field3|Field4|Field5'/
    'id1|name1|age1|DOB1|chr1,chr2,chr3'/
    'id2|name2|age2|DOB2|chr4,chr5,chr6'
    ;
run;

/* read the external file into the desired table structure
   - this code bit is close to what you need to implement in the external file object
*/
data read(drop=_:);
  infile extfile dlm='|' dsd truncover firstobs=2;
  input (Field1 Field2 Field3 Field4) (:$10.) _Field5 :$20.;
  length Field5 $10;
  _stop=sum(1,countc(_Field5,','));
  do _i=1 to _stop;
    Field5=scan(_Field5,_i,',');
    output;
  end;
run;

/* print the created SAS table */
proc print data=read;
run;