topic Re: Variable length import of a xlsm file in SAS Programming

Variable length import of a xlsm file

shoin — Mon, 24 May 2021 23:08:22 GMT

SAS 9.4 TS1M7 on WIN x64

ACCESS/PCFF is installed

Trying to proc import a XLSM file in SAS EG, the only way been able to do so is to set one if its column (due to variable nature of length/data) the column to 29k length and insert 19k worth of gibberish (call it some story) pad the column which is then imported then the gibberish is stripped off.

Do you guys know of a better way?

Sincerely,

Re: Variable length import of a xlsm file

ballardw — Mon, 24 May 2021 23:12:03 GMT

My first approach would be get rid of the macro(s) in the file. SAS won't use them.

Possibly save the file/sheets as CSV (comma separated values) and read/import the CSV file(s).

Re: Variable length import of a xlsm file

shoin — Mon, 07 Jun 2021 16:16:17 GMT

(Solved for me)

/* dbms=excel was used, where sas & MS office prod has same bitness */
/* col 1 had variable length entries */
proc import file="e:\data\mydata_xlsm_vl.xlsm" dbms=excel out=test_vl;
run;