topic Re: How I can split character variable into multiple rows?? in Statistical Procedures

How I can split character variable into multiple rows??

hyunjoo_min — Sat, 06 Sep 2014 00:35:13 GMT

I have a dataset like this.

What I want to do it with SAS is split one blank into multiple rows

for example:

Investor Group

STL PLC

LSI Logic Corp

are in one blank

I want to split it into 3 rows

How can I do this with SAS??

Re: How I can split character variable into multiple rows??

Patrick — Sat, 06 Sep 2014 02:29:14 GMT

Please attach a sample of your data as a .csv file.

From what I can see I would assume that the data needs to be read into SAS row by row. The values in a single cell will map as a single string into a SAS variable. I assume that this single string breaks so nicely within a cell because there are line feeds (LF) in these strings. If so then one could parse the strings and split them up into multiple observations using LF as the delimiter.

But: Looking at your screen-shot the values in the last column look like something which then would have to be repeated for all rows.

If you want us to provide a realistic solution you need to attach a sample of your real data.

Re: How I can split character variable into multiple rows??

PGStats — Sat, 06 Sep 2014 02:40:29 GMT

Assuming your data resides in an Excel workbook, you could try something like this:

libname xl Excel "&sasforum.\Datasets\hyunjoo DATA.xlsx" access=readonly mixed=yes;

data want;

set xl.'Sheet1$'n;

array F

_character_;

array F_[20] $200 _temporary_; /* 20 is >= the number of columns in the table */

do i = 1 to dim(F);

F_ = F;

end;

do j = 1 to 10; /* 10 is >= the max number of lines in a cell */

do i = 1 to dim(F);

F = scan(F_,j,'0A'x,'M');

end;

if catx(of F

) ne "" then output;

end;

drop i j;

run;

libname xl clear;

proc print data=want noobs; run;