BookmarkSubscribeRSS Feed
ark123
Obsidian | Level 7

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!

3 REPLIES 3
Tom
Super User Tom
Super User

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

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

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;

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 271 views
  • 0 likes
  • 2 in conversation