BookmarkSubscribeRSS Feed
hyunjoo_min
Calcite | Level 5

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

2 REPLIES 2
Patrick
Opal | Level 21

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.

PGStats
Opal | Level 21

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

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is ANOVA?

    ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

    Find more tutorials on the SAS Users YouTube channel.

    Discussion stats
    • 2 replies
    • 2499 views
    • 0 likes
    • 3 in conversation