topic Re: How to import excel with "multiple sheets" and combine them? in SAS Programming

How to import excel with "multiple sheets" and combine them?

tinghlin — Mon, 19 Oct 2020 04:36:37 GMT

I have an excel files with 3 work sheets for year 106, 107 and 108. each year has 100 observations with the same varoables. I wrtoe an macro, but WORK.a has only 100 observations. how do I merge the 300 obsevations together into work.a ?

%macro pim(sheet);
proc import out= WORK.a
datafile = 'G:\data\106_108.xlsx'
DBMS=EXCEL replace;
sheet = "&sheet";
GETNAMES=YES;
run;
%mend pim;
%pim(106);
%pim(107);
%pim(108);

proc print data= WORK.a ;
run;

Re: How to import excel with "multiple sheets" and combine them?

andreas_lds — Mon, 19 Oct 2020 05:05:09 GMT

See Maxim 2: The log shows that each call of your macro creates/overwrites the same dataset. So you have to write each sheet to a different dataset first, than append them into a single dataset. Please note, that the last task may sound easy, but can be challenging, because excel doesn't have proper variable-types, forcing proc import to guess the types and set the length of alphanumeric variables depending on the values found in the data, most likely resulting in different lengths for a variable that seems to identical in all sheets.

Re: How to import excel with "multiple sheets" and combine them?

Shmuel — Mon, 19 Oct 2020 05:29:20 GMT

As your excel file is of xlsx type, you can try next code:

libname myxlsx xlsx "G:\data\106_108.xlsx";

data want;
  set myxlsx.'106'n
      myxlsx.'107'n
      myxlsx.'108'n
  ;
run;

it will be better if sheet names will be renamed into "s016", "s107", "s108"

then the code can be adapted to:

libname myxlsx xlsx "G:\data\106_108.xlsx";

data want;
  set myxlsx.s106
      myxlsx.s107
      myxlsx.s108
  ;
run;