BookmarkSubscribeRSS Feed
Fluorite | Level 6



I have been trying to read some excel data into the SAS stored processes. I use SAS Add-in for microsoft office. 

The problem here is, the data is read into SAS but when I input some text into excel i multiple lines using Alt+Enter, the data is still read and stored in a single line. (When I read the data in EG /output this back to excel, I get those two lines concatenated as a sinle line).



Excel cell value contains text in multiple lines (entered using Alt+Enter): 

This is line one of my text

Here is line two.


This value is stored as This is line one of my text Here is line two.


Note : My text does not have a particular pattern.


I am calling the stored process from VBA. Below are the configurations in my stored process.

Input data type : XML Data Source 

Expected content type: text/xml.


I use the below code in stored process to read and save the excel cell as dataset.

libname myexcelrange xml;

data mylib.dummy;
	set myexcelrange.excel_table;

Please help me in storing and retrieving this data in multiple lines.

Quartz | Level 8

I do not know much about xml format, but I just tried to enter your text using Alt + Enter. Although the entered texts appear to be in separate lines in Excel, they are still in a single field (they still take up one row and one column). Your texts also do not seem to have any delimiters other than blanks. My suggestion would be to enter the lines in different rows in Excel.
Super User Tom
Super User

When you read the data out of Excel sheet into a SAS dataset each cell in the sheet becomes the value of one variable in one observation.


You want to split it into multiple observations (or multipe variables) then use the SCAN() function.  Check your actual data to see if the lines are split by linefeed characters ('0A'x) or carriage return characters ('0D'x).


So if you read the data into a dataset named HAVE and the variable that has to "split" cells is name MYVAR then this code will create a separate observation for each value.  With the new variable I having the count of which value it was and the new variable SPLITVAR having individual part.


data many_lines;
   set have;
   length splitvar $100;
   do i=1 by 1 until(i>countw(myvar,'0A'x));

Here is the way to split them into multiple variables instead. Note that you need to set a fixed upper bound on the number of lines thta a cell could contain.  In this example I used 10.  So there will be 10 new variables SPLITVAR1 to SPLITVAR10. And I should have either the number of values or 11 if there were more than 10.

data many_vars;
   set have;
   array splitvar [10]  $100;
   do i=1 to dim(splitvar) while (i<=countw(myvar,'0A'x));

For both examples I made the new variable have room for only 100 characters.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3 in conversation