@dennis_oz wrote:
Requirement 1 -> read all multiple excel files as in multiple structures as character .
cannot convert it to csv
Except for the approach @Vince_SAS proposes - which I'm not sure is already available in SAS 9.3 - there is no direct way to read all source data into character variables only.
I believe if reading from an Excel source SAS will always attach a format to the created variables. If so then you could try if code as below does what you're after.
%macro convertAllVarsToChar(inds, outds=);
%let inds=%upcase(&inds);
%if &outds= %then %let outds=&inds;
%local lib tbl;
%let lib=%scan(WORK.&inds,-2,.);
%let tbl=%scan(&inds,1,.);
proc sql;
create table want_meta as
select *
from dictionary.columns
where libname="&lib" and memname="&tbl"
order by varnum
;
quit;
filename codegen temp;
data _null_;
/* file print;*/
file codegen;
set want_meta end=last;
length nm_in nm_out $35;
nm_in =cats("'",name,"'n");
nm_out=cats("'_",substrn(name,1,31),"'n");
if _n_=1 then
do;
put
"data &outds;" /
@3 "set &inds;"
;
end;
if type='num' then
do;
if format='BEST.' then format='BEST16.';
put @3 nm_out '= put(' nm_in ',' format ');';
end;
else
put @3 nm_out '=' nm_in ';';
put
@3 'drop ' nm_in ';' /
@3 'rename ' nm_out '=' nm_in ';'
;
if last then
put 'run;';
run;
%include codegen / source2;
filename codegen clear;
%mend;
proc import
datafile='~/test/test.xlsx'
out=want
dbms=xlsx
replace
;
run;
title 'Before Conversion';
proc contents data=want;
run;
%convertAllVarsToChar(want);
title 'After Conversion';
proc contents data=want;
run;
proc print data=want;
run;
... View more
