topic Re: Importing and combining excel workbooks in SAS in SAS Programming

Importing and combining excel workbooks in SAS

2222 — Fri, 17 Jun 2022 08:18:22 GMT

Hi all,

I am fairly new to SAS and could really use some help with the following please:

I have 3 excel workbooks (not sheets in 1 workbook) and I need to make them into a single SAS data table if possible. One catch is that the workbooks have some columns that have the same data (headings and data is the same), but also some unique columns (present in only one workbook and not the others). When I try the below code, I get a whole bunch of warnings "...was not found in base. The variable will not be added to the BASE file."

Ideally I'd like to have the new data from workbook 2,3,4 etc.. be added to the data table, and in the case that some of the data is duplicated have a way of only showing it once in the data table, is that possible?

%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;
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=file containing excel files,out=merged);

Re: Importing and combining excel workbooks in SAS

PaigeMiller — Fri, 17 Jun 2022 11:32:00 GMT

It seems as if your question boils down to the ability of PROC APPEND to actually append these files. (I haven't checked the rest of your macro)

According to the PROC APPEND documentation

Appending to Data Sets with Different Variables

If the DATA= data set contains variables that are not in the BASE= data set, use the FORCE option in the APPEND statement to force the concatenation of the two data sets. The APPEND statement drops the extra variables and issues a warning message. You can use the NOWARN option to suppress the warning message.

So, you can't use PROC APPEND in this case. You would need to do the append in a DATA step.

Re: Importing and combining excel workbooks in SAS

Tom — Fri, 17 Jun 2022 11:57:33 GMT

You could try just combining the datasets instead of trying to use PROC APPEND.

That will allow the addition of new variables.

data out;
  set out _test;
run;

But with PROC IMPORT you could also have other issues.

Similarly named variables might be of different types.

Character variables might have different lengths.

To properly deal with that programmatically you would need to save the individual imported dataset and compare the metadata (variables and their type, length and attached formats) to determine if any need to have their type changed to make combining possible. And to calculate the proper length for character variables to avoid truncation of data.