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;
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.
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.
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.
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
The end date column is messed up due to "?5/31/2017"
That's why I think about data step.
@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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.