topic Re: Import xls All character Same length in SAS Programming

Import xls All character Same length

dxhuan — Wed, 18 Mar 2015 02:30:20 GMT

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.

Re: Import xls All character Same length

Ksharp — Wed, 18 Mar 2015 12:11:58 GMT

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

Re: Import xls All character Same length

dxhuan — Wed, 18 Mar 2015 12:38:25 GMT

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.

Re: Import xls All character Same length

Ksharp — Wed, 18 Mar 2015 12:46:38 GMT

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

Re: Import xls All character Same length

RW9 — Wed, 18 Mar 2015 13:30:02 GMT

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

Re: Import xls All character Same length

data_null__ — Wed, 18 Mar 2015 13:52:14 GMT

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.*/

Re: Import xls All character Same length

dxhuan — Thu, 19 Mar 2015 12:49:10 GMT

Thank you very much, data_null_. It works well.