01-24-2018 03:06 PM
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
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.
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 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.