BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

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!

6 REPLIES 6
ballardw
Super User

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
Lapis Lazuli | Level 10

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

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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

Data never sleeps
GeorgeSAS
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1081 views
  • 1 like
  • 3 in conversation