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