topic Re: Split cells based on soft enter in SAS Procedures

Split cells based on soft enter

Giovani — Sun, 26 Sep 2021 12:09:34 GMT

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.

Re: Split cells based on soft enter

Ksharp — Sun, 26 Sep 2021 12:26:44 GMT

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;

Re: Split cells based on soft enter

Giovani — Sun, 26 Sep 2021 12:47:40 GMT

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

Re: Split cells based on soft enter

Ksharp — Sun, 26 Sep 2021 12:56:44 GMT

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;

Re: Split cells based on soft enter

Giovani — Sun, 26 Sep 2021 15:48:37 GMT

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;

Re: Split cells based on soft enter

Tom — Sun, 26 Sep 2021 16:07:41 GMT

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;

Re: Split cells based on soft enter

Peter_C — Sun, 26 Sep 2021 16:26:56 GMT

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 ;

Re: Split cells based on soft enter

Giovani — Sun, 26 Sep 2021 19:03:16 GMT

This solution also works!