topic Re: Split dataset (one colum - fixed length data) into columns in SAS Programming

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

Peter_Wine — Wed, 18 Nov 2020 16:02:04 GMT

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

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

PaigeMiller — Wed, 18 Nov 2020 16:05:07 GMT

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

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

Peter_Wine — Wed, 18 Nov 2020 16:23:14 GMT

Works, thanks.

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

Many thanks.

Piotr

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

PaigeMiller — Wed, 18 Nov 2020 16:33:04 GMT

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.

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

data_null__ — Wed, 18 Nov 2020 16:41:44 GMT

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;

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

ballardw — Wed, 18 Nov 2020 17:02:01 GMT

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.

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

Peter_Wine — Wed, 25 Nov 2020 13:15:36 GMT

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