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: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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