How remove spacing text data set on next line

[ark123]

Hello, 

I have two variables which contain text data. Some of the text is set on multiple lines, but I would like the text to be on one line with one space between each word. Example of current format:

Well

Baby

Example of desired format:

Well Baby

I have tried two different things. First Try:

data final;
	set data;
	location=tranwrd(location,'Well
Baby','Well Baby');
	quality=tranwrd(quality,'Needs
Repeat
Test','Needs Repeat Test');
run;

When I export the above output to excel, the next line spacing is still there. I used ods excel and proc report.

Second Try:

data final;
	set data;
	location_compbl = compbl(location);
	quality_compbl = compbl(quality);
run;

When I export to excel, the next line spacing goes away when I choose 'wrap text' but then there are no spacing between the words. I used ods excel and proc report.

Thanks in advance for your suggestions!

[Tom]

So you say you have a VARIABLE.  So that must be in a DATASET.  And you want to collapse the data from multiple OBSERVATIONS into one observation.

How do you know which observations get combined?

It the dataset is TINY then just collapse it into one observation.

data want;
  set have end=eof;
  length new_var $4000 ;
  retain new_var;
  new_var = catx(' ',new_var,old_var);
  if eof ;
  drop old_var;
run;

Otherwise you need some variable (or set of variables) then uniquely identify the observations that go together.  (And also perhaps some other variable that indicates the ORDER they should have in the new combined string).

data want;
  set have end=eof;
  by group order ;
  length new_var $4000 ;
  retain new_var;
  if first.group then new_var=' ';
  new_var = catx(' ',new_var,old_var);
  if last.group ;
  drop old_var order;
run;

[ark123]

Hi Tom, 

Thanks for your suggestion. Unfortunately, that did not work for me - it removed all but one observation in my dataset. To clarify, I have a dataset with 5,000 records and 50 variables. One of these variables is location ('well baby' 'nicu' and 'other'). Another variable is quality ('Acceptable' and 'Needs Repeat Test'). When I was exporting to excel, the words were on individual lines:

Well

Baby 

Instead of:

Well Baby

OR:

Needs

Repeat

Test

Instead of:

Needs Repeat Test

In attempting your suggestion and viewing the output in excel, I realized the solution is to export the file to excel differently. When I export using proc export excel, the words are formatted on one line with proper spacing between each word. Thanks for your input.

Solution: use proc export rather than ods excel file

[Tom]

So your problem was with the EXCEL file and NOT with either the SAS dataset or a text file with LINES in it.

Instead what ever method you used to produce a REPORT into an XLSX wrapped the lines.  ODS EXCEL will actually insert line breaks into the long strings to make the report look better, unless you tell it not to do that.

You can also use the XLSX libname engine to copy data to an XLSX file.

This code will create a sheet named mydata in the XLSX file named myfile.xlsx from a work dataset named mydata.

libname out xlsx 'myfile.xlsx';
data out.mydata;
  set mydata;
run;