topic Re: Help with import excel file in SAS Programming

Help with import excel file

hhchenfx — Mon, 25 Mar 2019 01:50:49 GMT

Hi Everyone,

Can you help me to fix my code to import Excel file attached?

I thought I listed everything here but still I cant import it.

Even when I put only " input Firm :$50. ; " it still import only 1 row with weird character!

Thank you so much.

HHCFX

	data ccrate ();
	infile "C:\_temp\Subs sample.xlsx" 	;
	input 	Firm :$50. user :$100. Start_Date :mmddyy10. End_date :mmddyy10.  Project:$100. Type :$30. value code :$50. 
hire :$30.	Location:$50.  total  	 Conversion_Date :mmddyy10.;
	format Start_Date end_date Conversion_Date :mmddyy10.;run;
	;run;

Re: Help with import excel file

ChrisNZ — Mon, 25 Mar 2019 02:29:25 GMT

XLSX files are ZIP files.

The INPUT statement is to be used with TEXT files; ZIP files will display as "weird characters".

Use PROC IMPORT to read XLSX files.

Re: Help with import excel file

ScottBass — Mon, 25 Mar 2019 02:38:04 GMT

proc import 
   datafile="Path\To\Subs sample.xlsx"
   out=foo (where=(firm is not missing))
   dbms=xlsx
   replace;
   getnames=yes;
run;

Stop trying to import a zipped XML file (i.e. a binary file) via an infile statement.

Read the doc on PROC IMPORT for more details.

Re: Help with import excel file

hhchenfx — Mon, 25 Mar 2019 02:44:54 GMT

Thanks for your help.

I can import it with Proc import but then in the Date column, there are record like "2/02/2019!" and SAS import the whole column as text.

That's why I want to try the datastep where I can clarify the format of column.

ERROR: You are trying to use the numeric format MMDDYY with the character variable Start_Date in data
set WORK.CRATE.
ERROR: You are trying to

Re: Help with import excel file

hhchenfx — Mon, 25 Mar 2019 02:48:11 GMT

The end date column is messed up due to "?5/31/2017"

That's why I think about data step.

Re: Help with import excel file

ScottBass — Mon, 25 Mar 2019 05:43:50 GMT

@hhchenfx wrote:
The end date column is messed up due to "?5/31/2017"
That's why I think about data step.

These are the values for end date (Cells D9 and D10):

?5/31/2017
9/30/2017!

PROC IMPORT uses the "GuessingRows" option to make a best guess as to the data type of the column based on the data within.

What do you want SAS to do with these cells? As far as SAS is concerned, this is just text, and it has imported it as such, converting the entire column to text.

I tried to apply the mm-dd-yy date format in Excel so the columns would be formatted consistently (mm-dd-yy vs. mm/dd/yyyy). Even Excel doesn't know what to do with these cells.

Any chance you can clean up your data?

Re: Help with import excel file

Kurt_Bremser — Mon, 25 Mar 2019 07:28:23 GMT

The best advice we can give with regards to Excel files is "don't use them".

Save your data to a usable format (textual, like csv or fixed-width), and read the data with a custom written data step, where you can take care of special cases and the necessary conversions yourself.