03-17-2015 10:30 PM
Hi SAS Users,
I have a bunch of xls files (too many for manual adjustment). I want to read them into SAS with the types of all columns being character. And I also would like to force all the columns to have the same length, say $5000. Within each xls, I have 60 columns, so i'd prefer programming to manual change.
My SAS programming skill is intermediate I would say. And according to my knowledge, neither PROC IMPORT nor the LIBNAME excel engine can easily achieve this. Is DATA step my best option? Anybody any help? Thanks.
03-18-2015 08:11 AM
You'd better transform all these XLS files into CSV files . Search a tool to do that in Google .
And after that :
infile dummy filevar=fname .........
input ( var1-var60) (: $200.);
03-18-2015 08:38 AM
Thanks. I tried to transform the XLS to CSV. But there's one column containing ";" separating names. When I save the XLS as CSV, all information after the semicolons are lost. I need to keep these semicolons for future use, so i can't dump these semicolons either. I've also tried to transfer XLS to TXT. Failed too.
03-18-2015 09:52 AM
You could use the LIBNAME engine and DBSASTYPE to achieve what you want. It takes a bit of doing (code gen);