BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giovani
Obsidian | Level 7

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.

 

AHMES-7.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

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 ;

 

 

  

View solution in original post

7 REPLIES 7
Ksharp
Super User

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;

Ksharp_0-1632659201763.png

 

Giovani
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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
Obsidian | Level 7

This solution also works!

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1828 views
  • 3 likes
  • 4 in conversation