Read multiple lines from an excel cell into SAS dataset using Excel-SAS Add-in.

[Karthveeryarjun]

Hi,

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

Example:

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;
run;

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

[aaronh]

Hi,

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.

[Tom]

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));
     splitvar=scan(myvar,i,'0A'x);
     output;
   end;
run;

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));
     splitvar[i]=scan(myvar,i,'0A'x);
   end;
run;

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