topic Re: How to read excel wrapped cell correctly in to SAS? in SAS Procedures

How to read excel wrapped cell correctly in to SAS?

GeorgeSAS — Tue, 15 Mar 2016 19:55:25 GMT

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!

Re: How to read excel wrapped cell correctly in to SAS?

ballardw — Tue, 15 Mar 2016 20:23:23 GMT

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

Re: How to read excel wrapped cell correctly in to SAS?

GeorgeSAS — Tue, 15 Mar 2016 20:56:29 GMT

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

Re: How to read excel wrapped cell correctly in to SAS?

ballardw — Tue, 15 Mar 2016 21:30:02 GMT

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

Re: How to read excel wrapped cell correctly in to SAS?

GeorgeSAS — Wed, 16 Mar 2016 12:34:55 GMT

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!

Re: How to read excel wrapped cell correctly in to SAS?

LinusH — Wed, 16 Mar 2016 13:19:32 GMT

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).

Re: How to read excel wrapped cell correctly in to SAS?

GeorgeSAS — Wed, 16 Mar 2016 19:19:22 GMT

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),", "))