Help using Base SAS procedures

Proc Import DBMS=XLSX Issue importing value from cell with formula

Reply
Occasional Contributor
Posts: 5

Proc Import DBMS=XLSX Issue importing value from cell with formula

I am using the Proc Import to bring in an XLSX.  I have ran into a problem where if the cell has a formula in it, it will not import the cell value and instead only impor the value of "A" for that cell.  Does someone know how to fix this issue?

Proc Import Datafile="&xlsx./L5 &filename..xlsx"

Out=&s_name._&s_tab. REPLACE

DBMS=xlsx;

Sheet= "&tab.";

Run;

Super User
Posts: 19,789

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

Posted in reply to MichelleBL

What would the value of the cell be? Is the type/length consistent with the rest of the values in the column?

Occasional Contributor
Posts: 5

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

It is a character column.  It reads in the column headers and everything that does not have a formula.  It just seems to have an issue when there is a formula present in the cell.

Super User
Posts: 11,343

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

Posted in reply to MichelleBL

By any chance is that resolved cell value longer than for the first 20 rows or so?

Occasional Contributor
Posts: 5

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

No the value isn't, the formula is.

Occasional Contributor
Posts: 5

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

Posted in reply to MichelleBL

If I put in the value and get rid of the formula it imports fine.  But I can't do this on every doc/every sheet I am bringing in.

Super User
Posts: 19,789

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

Posted in reply to MichelleBL

This might be a tech support question, see the very fine print at the bottom of this page - Submit a problem report. The answer may depend on your OS, version of SAS, version of Excel.

Or can you include a sample of your Excel file?

Occasional Contributor
Posts: 5

Re: Proc Import DBMS=XLSX Issue importing value from cell with formula

Thank you, I submitted a problem.  Hopefully they can figure it out.

Ask a Question
Discussion stats
  • 7 replies
  • 287 views
  • 0 likes
  • 3 in conversation