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
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
I don't know of a way using pass through, otherwise, look into DBSASTYPE option.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.