topic Re: how to arrange the observation in a variable to display exactly in excel sheel in SAS Programming

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

SASuserlot — Tue, 06 Oct 2020 04:02:45 GMT

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.

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

jimbarbour — Tue, 06 Oct 2020 05:13:35 GMT

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

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

ChrisNZ — Tue, 06 Oct 2020 06:30:33 GMT

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

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

Kurt_Bremser — Tue, 06 Oct 2020 07:09:30 GMT

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?

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

SASuserlot — Tue, 06 Oct 2020 14:02:51 GMT

you suggesting to concatenate with these during the data creation?

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

SASuserlot — Tue, 06 Oct 2020 14:06:04 GMT

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

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

jimbarbour — Tue, 06 Oct 2020 16:43:34 GMT

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

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

ChrisNZ — Tue, 06 Oct 2020 19:41:13 GMT

Yes indeed.

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

SASuserlot — Tue, 06 Oct 2020 21:56:39 GMT

worked it for me. Thanks