Split cells based on soft enter

[Giovani]

Hi,
I have a table with content that needs further exploration, placed all in one cell and separated by soft enter. Each entry is accompanied by information like day, ID etc. Any help in how I can break the cell into lines at the soft enter character?
The top part of the image represents what is in the excel file. The down part is just a paste-in word document to visualize the breakpoints. Excell attached.

[Ksharp]

You want this ?

data have;
 set sashelp.heart;
 have=catx('(*ESC*)n',status,sex,Weight_Status,Smoking_Status);
 keep have;
run;


ods excel file='c:\temp\temp.xlsx';
proc print data=have(obs=100);run;
ods excel close;

[Giovani]

Hi @Ksharp;
Not really, I want the column "have" to be breeake into different lines. Cell 1 would generate 4 lines:
1 Dead
1 Female
1 Overweight
1 Non-smoker

[Ksharp]

OK. You want soft enter as delimiter ?

Try this one :

data have2;
 set have;
 do i=1 to countw(have,'0D0A'x);
   have2=scan(have,i,'0D0A'x);output;
 end;
run;


ods excel file='c:\temp\temp.xlsx';
proc print data=have2(obs=100);run;
ods excel close;

[Giovani]

Maybe I wasn't very clear on my target. The initial input file has the soft enter as delimiter and I want to break those cells based on the soft enter to new lines. The target goal is to have a table organized as follow:

data want;
input Store $ Date $ Result $40.;
datalines;
B2120661 9/8/2021 Sale/Dishwasher-1: $20: Tom: 10.08
B2120661 9/8/2021 Sale/Dishwasher-2: $21: Anthony: 13.19
B2120661 9/8/2021 Return/Dishwasher-1: $20: Anthony: 11.380
B2120661 9/8/2021 Sale/Blender12: $5: Mary: 3.3
B2120661 9/8/2021 Sale/Blender-2: $5.1: Tom: 2.11

run;

[Peter_C · Accepted solution]

as you present it that way, I assume you have imported the "initial input file" and it has those multiple "lines" in the third column/cell and it is now in a sas dataset called "imported_data" which has that third column in sas variable "col3"; 

data better_data ;

set  imported_data ;

*drop col3  ;   ****once confident, drop col3 ;

 length  result $40 ;

 result = scan( col3, 1, "0a0d"x ); * allows 0Ax or 0Dx as soft delimiter ; 

 do col3_row= 1 by 1 while( result ne ' ' ) ;

     output ;

     result = scan( col3, col3_row+1, "0a0d"x ); 

 end ;

run ;

[Tom]

Just use the SCAN() function.

data want;
  set have ;
  do line=1 by 1 until(line >= countw(results,'0D0A'x));
    result_line = scan(results,line,'0D0A'x);
    output;
  end;
run;

[Giovani]

This solution also works!