CODEWITHSUNDEEP
sas
useR
useR

Reputation: 3082

SAS proc import .xls with several spreadsheet and append

Situation: i have a workbook .xls with 4 spreadsheets named "SheetA", "SheetB", "SheetC", "SheetD".

For import one spreadsheet i do as following.

proc import
    out = outputtableA
    datafile = "C:\User\Desktop\excel.xls"
    dbms = xls replace;
    sheet = 'SheetA';
    namerow = 3;
    startrow = 5;
run;

All spreadsheet have same number of variables and format. I would like to combine all four outputtableX together using data step:

data combinedata;
    set outputtableA outputtableB outputtableC outputtableD;
run;

I am new to SAS, i m thinking whether array and do-loop can help.

Upvotes: 1

Views: 10212

Answers (3)

Joe
Joe

Reputation: 63424

I would not use a do loop (as they're almost always overly complicated). Instead, I would make it data driven. I also would use Reese's solution if you can; but if you must use PROC IMPORT due to the namerow/datarow options, this works.

First, create the libname.

libname mylib excel "c:\blah\excelfile.xls";

We won't actually use it, if you prefer the xls options, but this lets us get the sheets.

proc sql;
  select cats('%xlsimport(sheet=',substr(memname,1,length(memname)-1),')')
    into :importlist separated by ' '
    from dictionary.tables
    where libname='MYLIB' and substr(memname,length(memname))='$';
quit;

libname mylib clear;

Now we've got a list of macro calls, one per sheet. (A sheet is a dataset but it has a '$' on the end.)

Now we need a macro. Good thing you wrote this already. Let's just substitute a few things in here.

%macro xlsimport(sheet=);
  proc import
    out = out&sheet.
    datafile = "C:\User\Desktop\excel.xls"
    dbms = xls replace;
    sheet = "&sheet.";
    namerow = 3;
    startrow = 5;
  run;
%mend xlsimport;

And now we call it.

&importlist.

I leave as an exercise for the viewers at home wrapping all of this in another macro that is able to run this given a filename as a macro parameter; once you have done so you have an entire macro that operates with little to no work to import an entire excel libname.

Upvotes: 2

Reeza
Reeza

Reputation: 21264

If you an xls file and are using a 32 bit version of SAS something like this would work:

libname inxls excel 'C:\User\Desktop\excel.xls';

proc datasets library=excel;
copy out=work;
run; quit;

libname inxls;

Then you can do your step above to append the files together. I'm not sure Proc Import with excel recognizes the option name row and start row so you may need to modify your code somehow to accommodate that, possibly using firstobs and then renaming the variables manually.

Upvotes: 1

DomPazz
DomPazz

Reputation: 12465

What you have will work assuming the variable names are the same. If they are not use the rename statement to make them all the same.

data combinedata;
set outputtableA(rename=(old_name1=new_name1 old_name2=new_name2 ... ))
    outputtableB(...)
    ...
    ;
run;

Obviously, fill in the ellipses.

Upvotes: 0

Related Questions