SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Proc import excel - Default character

Reply
Regular Contributor
Regular Contributor
Posts: 166

Proc import excel - Default character

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

Super Contributor
Posts: 291

Proc import excel - Default character

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

Esteemed Advisor
Posts: 5,085

Proc import excel - Default character

Try using the MIXED=YES option.

/Linus

Data never sleeps
Regular Contributor
Regular Contributor
Posts: 166

Proc import excel - Default character

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...

Esteemed Advisor
Posts: 6,890

Proc import excel - Default character

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?

Grand Advisor
Posts: 9,740

Proc import excel - Default character

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.

Contributor
Posts: 68

Re: Proc import excel - Default character

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.

Super Contributor
Posts: 254

Proc import excel - Default character

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?

Regular Contributor
Regular Contributor
Posts: 166

Proc import excel - Default character

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...

Super Contributor
Posts: 254

Proc import excel - Default character

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;

Regular Contributor
Regular Contributor
Posts: 166

Proc import excel - Default character

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..

Esteemed Advisor
Posts: 6,890

Proc import excel - Default character

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;

Grand Advisor
Posts: 9,740

Proc import excel - Default character

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.

Super User
Super User
Posts: 5,971

Re: Proc import excel - Default character

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;

Regular Contributor
Regular Contributor
Posts: 166

Proc import excel - Default character

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...

Post a Question
Discussion Stats
  • 18 replies
  • 17813 views
  • 7 likes
  • 9 in conversation