topic Re: Proc import excel - Default character in Microsoft Integration with SAS

Proc import excel - Default character

NN — Sun, 25 Sep 2011 12:22:22 GMT

Hi guys,

I wish to use PROC IMPORT to import a .XLS file.

Is it possible to import all my columns as character?

Please guide

Proc import excel - Default character

Bill — Tue, 27 Sep 2011 13:28:45 GMT

I believe that this needs to be done in excel prior to importing. Here's how:

Here's what you have to do to -really- ensure an Excel column is classified as text:

To REALLY change the format of an Excel column:

1) hilight the entire column

2) Click Data in menu bar

3) Choose Text to Columns

4) Click Next

5) Click Next

6) Column data format will say General, click Text radio button

7) Click Finish

Proc import excel - Default character

LinusH — Thu, 29 Sep 2011 08:25:58 GMT

Try using the MIXED=YES option.

/Linus

Proc import excel - Default character

NN — Wed, 12 Oct 2011 07:04:56 GMT

Hi Bill,

What you have suggested does work but what i wanted was that even my numeric columns should be read as character . i.e. I should not have to convert the column to character in the physical excel file.

Hi Linus,

The Mixed = yes would also not resolve my purpose in this condition.

Appreciate you help...

Proc import excel - Default character

GreggB — Wed, 16 Nov 2011 15:06:11 GMT

Is the basic problem that when you IMPORT the order of the variables changes from the Excel file when compared to the SAS data set?

Proc import excel - Default character

art297 — Wed, 16 Nov 2011 15:13:49 GMT

Out of curiosity, how many rows and columns do you have in the spreadsheet and is this a one-time need or code you are creating for a production environment?

Proc import excel - Default character

ballardw — Thu, 17 Nov 2011 00:13:24 GMT

I believe using IMPORT you may have to make the changes in Excel as SAS is requesting data that is processed through the JET or similar Excel engine. The engine tells SAS whether the values are numeric or string.

If you do not have a lot of workbooks/sheets to process you could save the worksheets as CSV and use the Proc Import for CSV. This alone doesn't fix your issue. However the proc import will create data step code to read the CSV file and you can change the INFORMAT statements to $ formats of the appropriate length.

Proc import excel - Default character

NN — Tue, 29 Nov 2011 19:09:23 GMT

Hi,

Sorry for the delayed reply.

Let me first put forward my concern. I have multiple users each of who upload an excel file to my sas directory. Post which a batch job runs to process these files Now there are two columns one is an ID and the other a Pincode. Now ideally these two columns are supposed to be character columns with numeric digits. But certain users may upload it as character in their excel where as others may upload it as numeric.

So i wanted to check if it was possible to import the data as character irrespective of the type in which the user has uploaded.

Though i am not facing any issues even if the columns are imported in numeric. I just wanted to know if what i was asking was possible.....

Judging from what i have read and others have suggested changing the physical file seems to be the only optiion available currently...

Thanks...

Proc import excel - Default character

GreggB — Tue, 29 Nov 2011 19:43:45 GMT

My earlier reply to your email was redundant to earlier replies. You may try this. when you run proc import you can go to your log and you'll see something like what's below. you can copy this from the log and paste it into your program editor. Then, let's say you want to change q_1 to numeric. Get rid of the $ sign on the informat q_1 line and also the format line (see bold highlights.) Now, submit this data step and it will change q_1 to numeric.

457 data WORK.CC_2 ;

458 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

459 infile 'C:\Documents and Settings\DASC\Desktop\SUGI05_CC_1.csv' delimiter = ',' MISSOVER

459! DSD lrecl=32767 firstobs=2 ;

460 informat q_1 $5. ;

461 informat q2_6 best32. ;

462 informat q7_9 $5. ;

463 informat q8_1 best32. ;

464 informat q8_3 $5. ;

.. more code ..

475 format q_1 $5. ;

476 format q2_6 best12. ;

477 format q7_9 $5. ;

478 format q8_1 best12. ;

479 format q8_3 $5. ;

489 format check_77 $5. ;

.. more code ..

490 input

491 q_1 $

492 q2_6

493 q7_9 $

494 q8_1

495 q8_3 $

.. more code ..

506 ;

507 if _ERROR_ then call symput('_EFIERR_',1); /* set ERROR detection macro variable */

508 run;

Proc import excel - Default character

NN — Wed, 30 Nov 2011 18:17:59 GMT

Hi Greg,

Will this work for .xls files? It think what you are suggesting will work if i was importing a .csv file.

But sadly i have to work with a .xls file.

Thanks..

Proc import excel - Default character

art297 — Wed, 30 Nov 2011 21:13:06 GMT

If you are on windows then another possibility is using DDE. Of course you would have to add the code to open Excel, wait for it to open, and then feed in the filenames, but that really isn't very difficult. The following doesn't include any of that code as, for a test, I simply had Excel open with a file I created that was a spreadsheet containing a copy of sashelp.class:

filename copyit dde 'Excel|c:\art\[test.xls]Sheet1!R1C1:R32000C5';

data want;

infile copyit dlm="09"x notab missover;

informat name $8.;

informat sex $1.;

informat age height weight $15.;

input name sex age height weight;

run;

Proc import excel - Default character

ballardw — Thu, 01 Dec 2011 21:53:33 GMT


Let me first put forward my concern. I have multiple users each of who upload an excel file to my sas directory. Post which a batch job runs to process these files  Now there are two columns one is an ID and the other a Pincode. Now ideally these two columns are supposed to be character columns with numeric digits.
 But certain users may upload it as character in their excel where as others may upload it as numeric.
So i wanted to check if it was possible to import the data as character irrespective of the type in which the user has uploaded.

Since it appears that you have users making a choice then the instructions to them could be modified from uploading the .xls to SAVE AS .CSV. Then what they have as the format in Excel won't matter.

This is an old problem. I ran into similar issues with reading Lotus 123 .PRN files into SAS 5 around 1991 with the added joy of getting them onto a mainframe from floppies. That experience is why I avoid spreadsheets as much as possible.

Re: Proc import excel - Default character

Tom — Thu, 01 Dec 2011 22:10:52 GMT

You will get in trouble if your digits include leading zeros.

But otherwise it might not be that hard to convert the variables to character strings. You can create new character variables and assign the values to them and use DROP and RENAME to get the variable names straightened out.

Try something like this.

data one ;
  input one two ;
cards;
1 2
2 3
run;
data two ;
  input one $ two ;
cards;
1 2
2 3
run;
data want1 ;
  set one ;
  length var1 var2 $12;
  var1 = left(vvaluex('one'));
  var2 = left(vvaluex('two'));
  drop one two;
  rename  var1=one var2=two;
run;
data want2 ;
  set two ;
  length var1 var2 $12;
  var1 = left(vvaluex('one'));
  var2 = left(vvaluex('two'));
  drop one two;
  rename  var1=one var2=two;
run;
proc compare data=want1 compare=want2;
run;

Proc import excel - Default character

NN — Fri, 02 Dec 2011 20:01:24 GMT

Hi art,

Sorry for not confirming earlier . Im working on a Unix platform .. Hence i think DDE might not work here...

Tom,

What you have suggested is what i am currently using... Thanks for the VValuex ... Got to learn something new today..

As Ballardw says i too prefer not using excel but had no choice here..

thanks again...

Proc import excel - Default character

GreggB — Wed, 07 Dec 2011 15:44:02 GMT

try this. modify the path to find your data. var is the name of column one so you may have to change it.

libname db "C:\excel files\test1.xls" mixed=yes;

   data test3;
     set db."Sheet1$"n(DBSASTYPE=(var='char(1024)'));
   run;

libname db;

Proc import excel - Default character

NN — Thu, 08 Dec 2011 06:49:46 GMT

Hi gregg,

The libname statement gives the following error

ERROR: Library DB is not in a valid format for access method RANDOM.

ERROR: Error in the LIBNAME statement.

I think what you have suggested would work on windows. But since i am on a Unix platform maybe it doenot work here.

Thanks...

Re: Proc import excel - Default character

kleinhev — Thu, 14 May 2015 05:15:02 GMT

Please see this thread:

Re: Proc import excel - Default character

agoldma — Sat, 18 Jun 2016 19:48:52 GMT

Mixed=Yes is a libname option, which works only for those Excel columns that contain both numbers and words. It interprets such mixed columns as character. It doesn't force all columns to be character.

Re: Proc import excel - Default character

agoldma — Sat, 18 Jun 2016 22:02:00 GMT

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character