I found a related post. In it, @novinosrin shared a macro that could be applied to a single SAS table and convert all variables to character:
/*macro to convert all numeric to char*/%macro vars(dsn);
%let list=;
%let type=;
%let dsid=%sysfunc(open(&dsn));
%let cnt=%sysfunc(attrn(&dsid,nvars));
%do i = 1 %to &cnt;
%let list=&list %sysfunc(varname(&dsid,&i));
%let type=&type %sysfunc(vartype(&dsid,&i));
%end;
%let rc=%sysfunc(close(&dsid));
data want(drop=
%do i = 1 %to &cnt;
%let temp=%scan(&list,&i);
_&temp
%end;);
set &dsn(rename=(
%do i = 1 %to &cnt;
%let temp=%scan(&list,&i);
&temp=_&temp
%end;));
%do j = 1 %to &cnt;
%let temp=%scan(&list,&j);
/** Change C to N for numeric to character conversion **/
%if %scan(&type,&j) = N %then %do;
/** Also change INPUT to PUT for numeric to character **/
&temp=PUT(_&temp,8.);
%end;
%else %do;
&temp=_&temp;
%end;
%end;
run;
%mend vars;
%vars(your_dataset_name)
Without success, I've tried to update the original bulk Excel file import function by inserting the %vars macro into the code. I was hoping that the macro will convert the columns in the newly created tables just before it is mended to the base table.
call execute('
proc import dbms=xlsx out= _test
datafile= '||fname||' replace ;
run;
%vars(_test);
proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
');
Thank you for the assistance.
... View more