BookmarkSubscribeRSS Feed
DaveBirch
Obsidian | Level 7

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.)

1 REPLY 1
MC1985
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 1 reply
  • 942 views
  • 0 likes
  • 2 in conversation