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.
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 ;
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;
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;
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;
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 ;
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;
This solution also works!
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!
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.