Simple formatting problem

[DJENS]

Hi everyone...

I am new here and have a simple formatting issue. I am using Proc Import to read in a worksheet in Excel. The first four columns are character and it correctly reads them in for me each and every time. The next "x" number of columns (never sure how many - anywhere from 1 column to 200 columns after column 4) are always numeric. But sometimes my data is messy and it reads it in differently than what I want. It will sometimes read it in as a numeric, sometimes as a "BEST" format and then sometimes as character. I want them to ALWAY be numeric. Is there a way to do this and make it flexible enough to take however many columns of "numeric" data and read it in correctly as the same numeric format for all the extra columns past column 4?

Thanks so much for your help!

Dave

[LinusH]

If your Excel cells contains strictly numerical data, PROC IMPORT should treat them as such. So, check that your columns only have numerical data. If so, be sure that they are formatted as numbers.

/Linus

[Doc_Duke]

DJENS & Linus,

Unfortunately, importing Excel files sometimes doesn't work the way it is documented. If you have several rows of missing values for a column, it may guess that it is character. Sometimes "cute" formatting will mess it up.

Also, SAS uses 3 different procedures to read Excel files (depending on the way you parameterize Excel to IMPORT), so DJENS may be using a different way than Linus or I do. Sometimes, the only way I have gotten an Excel file to read (in SAS 9.1.3) is by exporting it to a CSV file and IMPORTing the text (then you can use the GUESSINGROWS parameter).

You could also use DBMSCOPY (now a SAS product) or StatTransfer, they have sometimes worked when I have given up on native SAS.

Excel is a necessary evil as a data transfer tool; real database products are so much more civilized.

Doc Muhlbaier
Duke