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!
Is data in other cells in the same row supposed to be duplicated to go with the additional records from this cell or suppressed?
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
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
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!
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).
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),", "))
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: