Import xls All character Same length

[dxhuan]

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.

[Ksharp]

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.);

[dxhuan]

Hi Xia,

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.

[Ksharp]

That is really weird . then proc import it and transform all numeric into character .

[RW9]

Have just done a quick check and Excel seems to handle semicolons fine.  Post your WB or sample as it should work fine.

[data_null__]

You could use the LIBNAME engine and DBSASTYPE to achieve what you want.  It takes a bit of doing (code gen);

options validvarname=any validmemname=extend center=0;
libname x1 excel "system-specific pathname.xlsx";
proc contents noprint data=X1._all_ order=varnum out=names(keep=libname memname name varnum);
   run;
proc sort data=names;
   by libname memname varnum;
   run;
proc print;
   run;
filename FT76F001 temp;
data _null_;
   file FT76F001;
   length memname name $35;
   set names;
   by libname memname;
   if first.memname then do;
      memname = nliteral(memname);
      put 'data ' memname ';';
      put +3 'set ' libname '.' memname '(DBSASTYPE=(';
      end;
  
   name = nliteral(name);
   put +12 name "='char(200)'";
   if last.memname then do;
      put +12 '));';
      put +3 'run;';
      end;
   run;
%inc FT76F001 / source2;
libname x1 clear;
proc contents data=work._all_ order=varnum;
   run;


/*DBSASTYPE=(<column-name-1> =<SAS data-type> ...<column-name-n> = <SAS data-type> )*/
/*SAS data-type*/
/*specifies a SAS data type. SAS data types include CHAR(n), NUMERIC, DATETIME, DATE, TIME.*/

[dxhuan]

Thank you very much, data_null_. It works well.