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