topic Re: Combine multiple Excel files, forcing all variables to character, into a single SAS table in SAS Programming

Combine multiple Excel files, forcing all variables to character, into a single SAS table

avz — Wed, 09 Jun 2021 17:48:38 GMT

I'm using a macro function to import multiple Excel files and combine them into a single SAS table:

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;

if find(fname, "xlsx", 'it') then 
call execute('
  proc import dbms=xlsx out= _test
            datafile= '||fname||' replace ;
  run;
  proc append data=_test base='||out||' force; run;
  proc delete data=_test; run;
');
run;
filename myfiles clear;

%mend;

%MultImp(dir=G:\MY DRIVE\Folder\test,out=merged);

I'm running into the following error which results in null values:

WARNING: Variable zip_code not appended because of type mismatch.

I think it is because some files have zip_code stored as text and others have it stored as numeric. How can I update the function to force every column in each spreadsheet to character strings?

Re: Combine multiple Excel files, forcing all variables to character, into a single SAS table

Reeza — Wed, 09 Jun 2021 17:52:53 GMT

In your current methodology you cannot easily do this. You'll have to add in a step to your call execute that ensures that checks if the zip code is numeric and character and correct it - think data step/SQL. There's no way to control the types as you read them in from Excel because Excel doesn't enforce types in the columns.

Re: Combine multiple Excel files, forcing all variables to character, into a single SAS table

avz — Fri, 11 Jun 2021 11:43:02 GMT

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.