Programming the statistical procedures from SAS

How I can split character variable into multiple rows??

Reply
N/A
Posts: 1

How I can split character variable into multiple rows??

Data example.PNG

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??

Respected Advisor
Posts: 3,989

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

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.

Respected Advisor
Posts: 4,742

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

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;

    PG

    PG
    Ask a Question
    Discussion stats
    • 2 replies
    • 725 views
    • 0 likes
    • 3 in conversation