Split dataset (one colum - fixed length data) into columns

[Peter_Wine]

Hi All,

I've got a dataset (not file) which has only one column and millions of rows. This column contains a fixed length data.

How can I split this one column into columns according to fixed length of new columns?

Example:

Is:

Dataset_1:

| Col1                      |

| AAABBBCCC111 |

| AAABBBCCC222 |

| AAABBBCCC333 |

Need:

Dataset_2:

| Col1 | Col2 | Col3 | Col4 |

| AAA | BBB | CCC | 111 |

| AAA | BBB | CCC | 222 |

| AAA | BBB | CCC | 333 |

Thanks,

Piotr

[PaigeMiller · Accepted solution]

data want;
    set have;
    col1_new=substr(col1,1,3);
    col2_new=substr(col1,4,3);
    /* You type the rest, I'm lazy */
run;

[Peter_Wine]

Works, thanks.

I hoped that there is an easier method rather then typing "substring" n-times but Excel helped 🙂

Many thanks.

Piotr

[PaigeMiller]

Yes, there may be an easier solution if many more columns are involved, but for the example you gave, that's the solution I would use.

[data_null__]

Are all the fields length 3?  This could be modified to work with fields of different length

data test;
   input source $20.;
   cards;
AAABBBCCC111
AAABBBCCC222
AAABBBCCC333
;;;;
   run;
proc print;
   run;

data split;
   set test;
   array c[5] $3;
   pointer = addrlong(c1);
   *CALL POKELONG(source, pointer <, length> <, floating-point>);
   CALL POKELONG(source, pointer,length(source));
   run;
proc print;
   run;

[ballardw]

And a slightly less esoteric:

data test;
   input source $20.;
   cards;
AAABBBCCC111
AAABBBCCC222
AAABBBCCC333
;;;;

data want;
   set test;
   array c(4) $ 3;
   do i=1 to dim(c);
      c[i] = substr(source,(i-1)*3 +1, 3);
   end;
run;

but this does require the user to know how many variables to create.

[Peter_Wine]

Hi,

I just realized that using this solution I get as a result table which contains: old column and new columns.

So it looks that to old table new columns are added.

I want to keep only new columns.

And - I don't know if it is because of above - the length of new columns is set the same as length of old column. How to change it to appropriate length.

Please remember that my dataset has over 100 columns hence I ask to simple solution. 🙂

Thanks,

Piotr