SAS / Excel - Expand Single Cell Containing Alt+Enter into Rows

Question

( Beginner in SAS, some SQL, and some Excel experience (No VBA) )

I have a scenario where I wish to break-up a data dictionary in Excel into SAS data. The data dictionary contains Alt+Enters (line breaks) within a cell that I want to break up into separate rows.

My tools:

• SAS 9.4 on Windows 7
• Excel 2010

Actual Excel data (with Alt+Enter):

Variable     | Cell
Name         | Value
-------------------------
Var1         | Val1 = abc     <- there is an Alt+Enter
             | Val2 = def     <- there is an Alt+Enter
             | Val3 = ghi
-------------------------
Var2         | Val1 = jkl     <- there is an Alt+Enter
             | Val2 = mno
-------------------------
Var3         |
-------------------------
Var4         | Words

I want my final data (in SAS) to look like:

Variable     | Var      | Var
Name         | Code     | Value
-------------------------------
Var1         | Val1     | abc
Var1         | Val2     | def
Var1         | Val3     | ghi
-------------------------------
Var2         | Val1     | jkl
Var2         | Val2     | mno
-------------------------------
Var3         |          |
-------------------------------
Var4         | Words    |

In this case, I see that I need to:

1. Use the Excel line breaks (Alt + Enter) within a cell as a delimiter (row?)
2. Use the equal sign (=) as a delimiter
3. Maintain the variable name when I expand out the data values

Notes:

• There can be blank values in the original cell values
• There can be values without equal signs (=) in the original cell values

Looking for recommendations on the best way to perform this (e.g., just import the Excel file to SAS, and work with it in the SAS tool).

This is my current data import step to bring the Excel spreadsheet into SAS:

PROC IMPORT datafile="filepath" dbms=xlsx out=temp REPLACE;
RANGE="SheetName$A1:B";
DATAROW=2;
RUN;

Answer 1

Excel will place a carriage return where the Alt-Enter is pressed.

data want ;
  set temp;
  do i=1 to countw(cellvalue,'0d'x);
    varcode = scan(cellvalue,i,'0d'x);
    varvalue = scan(varcode,2,'=');
    varcode = scan(varcode,1,'=');
    output;
  end;
run;