title: SAS 9.3 W7x64 Excel2013 proc import problems when formulas in xlsx
body: <p>I am recently experiencing strange troubles when using proc import on multi-sheet xlsx-files that contain formulas.</p>
  
  <pre><code>proc import out = testfile
  datafile= "C:\myfile.xlsx"
  dbms = xlsx replace; 
  sheet =  "aaa" ; 
  GETNAMES = yes; 
  mixed=yes;
  run;
  </code></pre>
  
  <p>On sheet "aaa" there are formulas -- i.e. links to cells on sheet "ccc". After the import procedure into SAS one variable (containing strings only) does not contain the values that it should contain, but rather the name of a third sheet (call it "bbb") from myfile.xlsx.</p>
  
  <p>BEFORE IMPORTING INTO SAS:</p>
  
  <pre><code>sheet ccc:
  "bad_variable" (in column A) 
  correctname1
  correctname1
  correctname1
  correctname2
  ...
  
  sheet aaa (with formulas):
  "bad_variable"
  =ccc!A1
  =ccc!A2
  =ccc!A3
  =ccc!A4
  ...
  
  sheet bbb:
  "any_variable"
  whatever
  whatever
  whatever
  whatever
  ...
  </code></pre>
  
  <p>EXPECTED AFTER IMPORT INTO SAS:</p>
  
  <pre><code>testfile:
  "bad_variable"
  correctname1
  correctname1
  correctname1
  correctname2
  ...
  </code></pre>
  
  <p>WHAT TRUELY HAPPENS AFTER IMPORT INTO SAS:</p>
  
  <pre><code>testfile:
  "bad_variable"
  bbb
  bbb
  bbb
  bbb
  ...
  </code></pre>
  
  <p>I think that this might be a bug.
  A first idea points into the following direction:
  when unzipping the xlsx-file there pops up a file called "sharedStrings.xml". The string "bbb" happens to be the first string contained in this list. This might be coincidence but maybe it is not...?</p>
  
  <p>UPDATE 10.07.15: Seems like this is no coincidence: sharedStrings.xml contains all strings that are used in multiple instances throughout the document. Probably one of the reasons why xlsx files are smaller than xls files. Anyway: There seems to be a bug in the way that when using proc import links to cells that contain strings that are also part of the sharedStrings-list the values are replaced by the first entry of that list. Have already turned to tech support, will let you know about the answer.</p>
  
  <p>There seem to be no problems when the formulas are links to cells with numeric values. Only if the links refer to string values the described problems show up.</p>
  
  <p>Any help much appreciated!</p>
  
  <p>System environment:</p>
  
  <p>SAS 9.3 TS Level 1M2 ("Base SAS")</p>
  
  <p>SAS running on a metaframe server</p>
  
  <p>W7 64bit Enterprise</p>
  
  <p>MS Office 2013</p>