SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Inserting into a named range in an XLSX file

Reply
Contributor
Posts: 25

Inserting into a named range in an XLSX file

I'm trying to insert data a named range in an .xlsx file with the following code:

libname MyXLSX PCFILES path="&SrcPath.\Alt_Rptg_Tree.xlsx" SCAN_TEXTSIZE=NO;
PROC SQL ;
INSERT INTO MyXLSX.Acct_Chkr
   SELECT t1.Class_Description,
     t1.Sub_Class_Description,
     t1.Item_Description,
     t1.Cost_Element_Num,
     t1.Cost_Element_Description,
     t1.Rptg_SubClass_Name,
     t1.Rptg_SC_Order
   FROM PERM.REF_CE_TREE t1;
QUIT;

But, despite having the column attributes set to number in Excel (with Format Cells…), I get the following error:
ERROR: Value 4 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

When I view the properties of MyXLSX.Acct_Chkr they are all defined as char with $255 format.

Is there any way either in SAS or Excel to force the columns to match the type in my data?

(Using SAS9.4 through EG7.1 with a Windows server.)

Contributor
Posts: 34

Re: Inserting into a named range in an XLSX file

did you try the PCFILES libname options: MIXED=YES or  MIXED_DATA=YES or MIXED_DATATYPE ?

i found this SUGI PDF a good guide to start.

 

hope this help

let me know

have a nice day

 

MC

Martino Crippa
Ask a Question
Discussion stats
  • 1 reply
  • 300 views
  • 0 likes
  • 2 in conversation