BookmarkSubscribeRSS Feed
NN
Quartz | Level 8 NN
Quartz | Level 8

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

18 REPLIES 18
Bill
Quartz | Level 8

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

LinusH
Tourmaline | Level 20

Try using the MIXED=YES option.

/Linus

Data never sleeps
NN
Quartz | Level 8 NN
Quartz | Level 8

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

art297
Opal | Level 21

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?

ballardw
Super User

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.

agoldma
Pyrite | Level 9

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.

GreggB
Pyrite | Level 9

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?

NN
Quartz | Level 8 NN
Quartz | Level 8

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

GreggB
Pyrite | Level 9

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;

NN
Quartz | Level 8 NN
Quartz | Level 8

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

art297
Opal | Level 21

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;

ballardw
Super User

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.

Tom
Super User Tom
Super User

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;

NN
Quartz | Level 8 NN
Quartz | Level 8

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

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!

Discussion stats
  • 18 replies
  • 26059 views
  • 7 likes
  • 9 in conversation