How to read excel wrapped cell correctly in to SAS?

[GeorgeSAS]

Hello all;

The data in one excel cell looks like below, it should be four records but if I import the cell in SAS, they become one single record "ABC661204/38120440/39661204/3024". but I want them be seperated as four records or there will be a delimer ammong them(such as : "ABC***661204/38***120440/39***661204/3024". ) ,what should I do?

The data in ONE cell:

"ABC
661204/38
120440/39
661204/3024"

Thanks!

[ballardw]

Is data in other cells in the same row supposed to be duplicated to go with the additional records from this cell or suppressed?

[GeorgeSAS]

Just in this special column all cells has wrapped property,in this column,  multiple rows (from one to five) of records in one cell.

for the example:

"ABC
661204/38
120440/39
661204/3024"

the four rows of records are in one excel cell(wrapped) ,if I read it to SAS, it will give me "ABC661204/38120440/39661204/3024",

but I want this: "ABC***661204/38***120440/39***661204/3024"  (or any delimiter other than "***")

Thanks

[ballardw]

I wasn't clear enough. Do you have any other cells on the same row with any values at all?

If so, you need to make a decision about what to do with those values. Suppose you have a row of data that looks like this:

A                         B                          C (your combined cell)

John                    23                        "ABC 661204/38 120440/39 661204/3024"

Since you say the C needs to be splt into separate records do you want:

A                         B                          C (your combined cell)

John                    23                        ABC

John                    23                        661204/38

John                    23                        120440/39

John                    23                        661204/3024

Or should the output look like this?

A                         B                          C (your combined cell)

John                    23                        ABC

                                                        661204/38

                                                       120440/39

                                                        661204/3024

[GeorgeSAS]

Like the last, A,B and C belong to one obs. the problem is c itself has four records wrapped in one excel cell,if I read it in to SAS, the C became "ABC661204/38120440/39661204/3024". while I want c similar like this: "ABC@661204/38@120440/39@661204/3024"

Thanks!

[LinusH]

Having them on separate records directly sounds tough. Not very logical either. Think of the contents of cells as variable values (each numbered row is an observation, each char labeled column is a variable).

And sometimes does the value contain linefeed/carriage return.

What you can do is to replace your LF/CR with the delimiter of your choice once imported to SAS.

If you want to create multiple observations, scan your value for each part between LF/CR, and do an explicit output (in the data step).

[GeorgeSAS]

What a "data never sleeps"!

Thank you, I add a new column in excel which change alt+enter -- delimiter ,

=TRIM(SUBSTITUTE(SUBSTITUTE(L98,CHAR(13),""),CHAR(10),", "))