Import Excel file with Proc Import

[6656]

Hi, Anyone has expirence how to Proc Import a numeric column in Excel to be a Text variable in SAS dataset?
Thanks.

Mike

 

[jymm]

mike - make sure that excel knows that the field is REALLY a number ( I know - that sounds silly). SAS has problems loading 'General' format from Excel. In the past when I have had a problem uploading that has been it. Go into your Excel file - right click on the column - choose FORMAT CELLS --- choose NUMBER for the real number stuff and choose TEXT for the rest. This should help --- to make it easy on yourself make an Excel macro to do it for any file.

 

[6656]

Thanks JYMM,

Although I formated a column as TEXT for real number, the extracted SAS data variable stayed in number attribute with Proc Import. The most worest thing is that the charactor data will miss in SAS dataset when first record is numeric-text data and the rest maybe mixed num-char data in the text format column of Excel. The data is Ok (the variable is text atrribute) if the first record is a character data.

Thanks,
Mike

 

[6656]

Hi, I found the trick solution. Do format a cell/column to TEXT in Excel sheet first, and then enter the numeric values. So SAS Import can treat the column as TEXT.

Mike

 

[dje]

I have found that the excel file should not have blank rows after the labels at the top of the sheet. It works best with a solid block of data without the cosmetically added features some folk put in spreadsheets.