SAS/ACCESS 9.4: SQL Pass Through Facility for EXCEL - MIXED Option

craig159753 — Wed, 24 Jan 2018 20:06:42 GMT

Hi,

I am currently using the PROC SQL Pass Through Facility to import an EXCEL file. Now the import and code is working but it seems one variable is being imported as the wrong type. I have tried the MIXED option yet this does not seem to be working. As a bit of background below are some context

SAS Pass Through Facility

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=n12a4kxu1n231qn175rljheioy8a.htm&docsetVersion=9.4&locale=en

MIXED=YES Details

When reading data from Excel, SAS scans a column to determine if it is numeric or character, and if character, determine the width. Normally if an initially numeric column has subsequent character data, SAS will make the variable numeric and set the character values to missing. The “MIXED=YES” libname connection option tells SAS to convert such columns to character, thus saving the character entries.

The Problem

When importing the EXCEL sheet one column is automatically becoming numeric, assuming due to SAS reading the first few rows and making the determination the column is all numeric when later down the EXCEL sheet there are character values in the column.

Below is an example of the EXCEL Sheet

VAR1 | VAR2 |
Some | 3 |
Text | 45 |
Variable | 55 |
With | 4 |
Just | DATETIME20. |
Character | 44 |
Values | DATE9. |

The Code I use to import the EXCEL sheet

proc sql noprint;
    connect to excel (path = "mypath\myfile.xls" mixed=YES);
        create table mydataset as
        select *
        from connection to excel (select * from [myexceltabname$A2:S]);
    disconnect from excel;
quit;

The Output

The EXCEL sheet imports but the variable VAR2 is made a numeric variable and all character values are now null. Is there an option I can use to correct this, the MIXED option did not seem to do anything. Any help would be grateful, I should note Ideally I want to use the Pass Through Facility if possible.

Thanks

Craig

Re: SAS/ACCESS 9.4: SQL Pass Through Facility for EXCEL - MIXED Option

Reeza — Wed, 24 Jan 2018 20:15:03 GMT

I don't know of a way using pass through, otherwise, look into DBSASTYPE option.

topic Re: SAS/ACCESS 9.4: SQL Pass Through Facility for EXCEL - MIXED Option in SAS Programming

SAS/ACCESS 9.4: SQL Pass Through Facility for EXCEL - MIXED Option

Re: SAS/ACCESS 9.4: SQL Pass Through Facility for EXCEL - MIXED Option