BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

I am using the XLSX engine (on PC SAS 9.4M4) to read an Excel file.  Even if an Excel column has all numeric values, I would like to force it to be read into a character variable.  Because I will receive this file weekly, and it's possible that next week's data will have some character values in the column.  Is there a good way to do this?

 

Similar to this old post:

https://communities.sas.com/t5/SAS-Procedures/PROC-IMPORT-EXPORT-forcing-column-to-be-character-or-n...

 

I think what I want is something like the DBSASTYPE option, but it doesn't seem to be supported by the XLSX engine. 

 

Sample code:

libname myfile xlsx "%sysfunc(pathname(work))\myexcel.xlsx" ;

*write sashelp.class to excel ;
data myfile.class ;
  set sashelp.class ;
run ;

proc contents data=myfile.class ;
run ;

*Failing pseudo code to read in the Excel file, forcing Age to be character ;
data ClassCharAge ;
  set myfile.class(dbsastype=(Age='CHAR(20)')) ;
run ;

data ClassCharAge ;
  length Age $20 ;
  set myfile.class ;
run ;

 

I know there are workarounds like adding a row of dummy character data to the Excel file, or exporting the excel file to CSV before importing to SAS, or writing a macro to do the conversion.  And yes I hate reading from Excel just as much as everyone else.  Just wondering if there is a way to import numeric columns as character.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

I think this needs to be made as an enhancement request as the current driver does not support this.

View solution in original post

14 REPLIES 14
Shmuel
Garnet | Level 18

If you are going to receive this file weekly, why not define max length of variables, just before the SET staement,

as you have done in your last step.

 

 

Tom
Super User Tom
Super User

@Shmuel wrote:

If you are going to receive this file weekly, why not define max length of variables, just before the SET staement,

as you have done in your last step.

 

 


It is too late then.  The read from XLSX to SAS has already made the column as numeric/character based on what is this week's file.

Quentin
Super User

@Shmuel, because it doesn't work. : )

216  data ClassCharAge ;
217    length Age $20 ;
218    set myfile.class ;
ERROR: Variable Age has been defined as both character and numeric.
219  run ;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

I think this needs to be made as an enhancement request as the current driver does not support this.

Quentin
Super User

I added a SASware Ballot Idea to have XLSX support DBSASTYPE:

 

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-DBSASTYPE-or-similar-option-to-XLSX-engine-t...

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ballardw
Super User

Since Excel does not have any "data type" for any column any connection to Excel "guesses" what the variable type is.

 

Likely if you examine your column cell formatting that has numeric appearing values that you want as character you will find it is the Excel "general". If the column were "text" it might import as desired. Might.

 

So far the only reliable method I have discovered to import Excel files with any consistency and control is to save them as CSV and use a data step to read the CSV file. That allows controlling lengths and types of specific variables as well as variable names.

Quentin
Super User

Understood @ballardw.  I just wish there was a way to tell the XLSX engine that a column is character, instead of having it guess.  Since the XLSX engine is developed by SAS, rather than relying on Microsoft's ACE driver, I think there may be hope that SAS could give us this option.  I feel like Henry Feldman had the option in DBMS copy years ago, and he's been working for SAS for quite a while now... : )

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Kurt_Bremser
Super User

If I were you, I'd be strongly tempted to set up a process that uses OpenOffice (or a VB script for MS Office) in batch mode to convert the Excel to CSV, and then read that into SAS. If XCMD is enabled, it all could be done from SAS code.

Using a direct read from something as unreliable as Excel in a production environment gives me the creeps.

Shmuel
Garnet | Level 18

I agree with all have been said upto now.

 

When excel displays a value like 25.5 - it may be 25.4999 or 25.50001 etc.

What would you like to have in a char type variable ?

 

Anyway, you can still have it by "hard work" done once, like in next code:

data classchar;
 set myfile.class(rename=(
       age=v3 height=v4 weight=v5));
     length age $3 height $6 weight $8;
     age = put(v3, 2.);
     height = put(v4, best6.1);
     weight = put(v5, best8.1);
     drop v3-v5;
run;    

 

 

Shmuel
Garnet | Level 18
and it can be done programatically instead manually.
Quentin
Super User

@Shmuel asked:

When excel displays a value like 25.5 - it may be 25.4999 or 25.50001 etc.

What would you like to have in a char type variable ?

Fair question.  Probably just use whatever default best. format seems reasonable, just like SAS would do for any implied automatic numeric to character conversion.  Not that I'm a fan of automatic conversions, but when dealing with Excel, that would be the least of my problems. : )

 

I'd even accept an option which said "Forget about the values that are actually stored in Excel, just read the displayed (formatted) values into a character variable."

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ballardw
Super User

@Quentin wrote:

@Shmuel asked:

When excel displays a value like 25.5 - it may be 25.4999 or 25.50001 etc.

What would you like to have in a char type variable ?

Fair question.  Probably just use whatever default best. format seems reasonable, just like SAS would do for any implied automatic numeric to character conversion.  Not that I'm a fan of automatic conversions, but when dealing with Excel, that would be the least of my problems. : )

 

I'd even accept an option which said "Forget about the values that are actually stored in Excel, just read the displayed (formatted) values into a character variable."


If you apply a display type such as numeric with one decimal for a column and then do a file save as CSV the formatted value is what is saved in the CSV.

 

I determined that when I had problems with Excel files containing manually entered dates (gak, the things Excel can do with such things) and found that formatting a column with a single date type helped immensely prior to reading into SAS.

Vince_SAS
Rhodochrosite | Level 12

Can you use the PCFILES engine?

 

%let WORKPATH=%sysfunc(pathname(work));

*  Create test data;

libname xl_out xlsx "&WORKPATH\class.xlsx";

proc copy in=sashelp out=xl_out;
  select class;
run; quit;

libname xl_out clear;

*  Import the data;

libname xl_in pcfiles path="&WORKPATH\class.xlsx";

data work.class;
set xl_in.class(dbsastype=(Age='char(20)'));
run;

libname xl_in clear;

 

Vince DelGobbo

SAS R&D

 

Ksharp
Super User
You could use OS command to run a VBA(google it) to transform it into CSV ,and then import it .

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!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 13053 views
  • 6 likes
  • 7 in conversation