how to arrange the observation in a variable to display exactly in excel sheel

[SASuserlot]

I need to export the sas data set into Excel, I have the observations for variables with comma separated, I want them each  word/ Sentence after comma in a separate line. This variables were created by concatenating other variables

for example

Variable

Control, double blind, orthopedic, death

I want to display this as

Variable

control,

double blind,

orthopedic,

death

So that it will display in excel as it is. or any other suggestions also helps.

[jimbarbour]

Can you type some sample data, just a few records, into Excel and post a screen print?  I'm not quite sure what it is that you're trying to do.

If you can post some sample data in usable form (Datalines in a SAS program), that would be helpful.  I'm sure we can get to what you're trying to do.

Jim

[ChrisNZ]

Have you tried ending each line with  '0D'x or '0D0A'x  ?

[SASuserlot]

you suggesting to concatenate with these during the data creation?

[ChrisNZ]

Yes indeed.

[Kurt_Bremser]

Libraries contain datasets.

Datasets contain observations.

Observations contain variables.

Variables contain values.

A variable cannot contain observations, but it can contain several values, as in your case separated by commas.

So you want to display the comma-separated values of a single variable spread over several lines within a cell in Excel?

[SASuserlot]

sorry that I caused you confusion. In the above pic Yellow highlight indicates how the data displayed in Sas Dataset

Green highlight is how I want to display in Excel sheet, I tried with the highlight then wrap text in excel , however they  not separating the way I wanted. Any suggestions greatly appreciated. Thanks

[jimbarbour · Accepted solution]

Instead of a comma, you have to add a '2C0A'x separator.

Something like this:

ExcelCellValue = CATX('2C0A'x, 'orthopedic', 'diagnostic', 'Hypertension', 'vision problems leads to blindness', 'safety',  'Treatment');

Jim

[SASuserlot]

worked it for me. Thanks