[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: SAS-L: Import all excel columns as character
From: Quentin McMullen <qmcmullen.sas () GMAIL ! COM>
Date: 2020-01-27 13:50:55
Message-ID: 8471187148021624.WA.qmcmullen.sasgmail.com () listserv ! uga ! edu
[Download RAW message or body]
Thanks Roger,
I think your dbsastype only worked because your code implicitly used the default \
EXCEL engine (which uses Microsoft ACE driver in the background, and needs bitness \
matching, and only works on Windows).
My comment was the wish that the newer XLSX engine (which does not use MS ACE driver, \
and does not require bitness match, and works on *nix) would add support for the \
dbsastype option.
47 libname xl xlsx 'c:\junk\class.xlsx';
NOTE: Libref XL was successfully assigned as follows:
Engine: XLSX
Physical Name: c:\junk\class.xlsx
48 data work.sasClass;
49 set xl.class(
50 dbsastype=(
---------
22
ERROR 22-7: Invalid option name DBSASTYPE.
51 age='char(10)'
52 ));
53 run;
NOTE: The SAS System stopped processing this step because of errors.
Joe Schluter and Henry Feldman wrote a 2017 SGF paper comparing methods for reading \
excel files. https://support.sas.com/resources/papers/proceedings17/SAS0387-2017.pdf
I tend to agree with them that the XLSX engine seems like the best general choice, \
since it works without worrying about bitness, works on *nix, and is faster than the \
EXCEL engine.
Kind Regards,
--Q.
On Sun, 26 Jan 2020 17:01:10 -0500, Roger DeAngelis <rogerjdeangelis@GMAIL.COM> \
wrote:
> Good point Q
>
> There are other ways some involve casting using passthru or simple R or Python(not \
> shown) scripts
> Here are three other options but they require some? knowledge of the excel sheet. \
>
> 1. dbsastype and scan_text=no \
> 2. R colclass="character" \
> 3. Passthru \
>
>
> github (this solution) \
> https://tinyurl.com/ybx6lpp9 \
> https://github.com/rogerjdeangelis/utl-import-all-excel-columns-as-character-three-solutions \
>
> github \
> https://tinyurl.com/y8fb3kqe \
> https://communities.sas.com/t5/SAS-Data-Management/Reading-XLSX-file-and-change-the-all-column-type-into-character/m-p/523523 \
>
> Related repos \
>
> github \
> https://github.com/rogerjdeangelis/utl-excel-fixing-bad-formatting-using-passthru \
>
> SAS Forum \
> https://communities.sas.com/t5/SAS-Programming/PROC-IMPORT-XLS-engine-mixed-columns/m-p/502853 \
>
>
> * you need to know the variable names; \
>
> %utlfkil(d:\xls\class.xlsx); \
>
> libname xl 'd:\xls\class.xlsx'; \
> data xl.class; \
> set sashelp.class; \
> run;quit; \
> libname xl clear; \
>
> libname xl 'd:\xls\class.xlsx' scan_text=no ; /* the key is to not let it scan? */ \
> data work.sasClass; \
> set xl.class( \
> dbsastype=( \
> name='char(8)' \
> sex='char(1)' \
> age='char(10)' \
> height='char(10)' \
> weight='char(10)' \
> )); \
> run; \
> libname xl clear; \
>
>
>
> Variables in Creation Order \
>
> # Variable Type Len Format Informat Label \
>
> 1 NAME Char 8 $8. $8. NAME \
> 2 SEX Char 1 $1. $1. SEX \
> 3 AGE Char 10 $10. $10. AGE \
> 4 HEIGHT Char 10 $10. $10. HEIGHT \
> 5 WEIGHT Char 10 $10. $10. WEIGHT \
>
>
> %utl_submit_r64(' \
> library(xlsx); \
> library(Hmisc); \
> library(SASxport); \
> want<-read.xlsx("d:/xls/class.xlsx",1,colClasses=rep("character",5),stringsAsFactors=FALSE); \
> write.xport(want,file="d:/xpt/want.xpt"); \
> '); \
>
> libname xpt xport "d:/xpt/want.xpt"; \
> data want; \
> set xpt.want; \
> run;quit; \
> libname xpt clear; \
>
> Variables in Creation Order \
>
> # Variable Type Len \
>
> 1 NAME Char 7 \
> 2 SEX Char 1 \
> 3 AGE Char 2 \
> 4 HEIGHT Char 4 \
> 5 WEIGHT Char 5 \
>
>
> proc sql dquote=ansi; \
> connect to excel \
> (Path="d:/xls/class.xlsx" ); \
> create \
> table pasSas as \
> select \
>
> * \
> from connection to Excel \
> ( \
> Select \
> name \
> ,sex \
> ,format(age,'##') as age \
> ,format(height,'###.0') as height \
> ,format(weight,'###.0') as weight \
> from \
> [class] \
> ); \
> disconnect from Excel; \
> Quit; \
>
>
> Variables in Cr \
>
> # Variable Type Len \
>
> 1 NAME Char 255 \
> 2 SEX Char 255 \
> 3 AGE Char 1024 \
> 4 HEIGHT Char 1024 \
> 5 WEIGHT Char 1024 \
>
[prev in list] [next in list] [prev in thread] [next in thread]