SAS Macro: Exporting tables to different sheets within one Excel file

Question

SAS beginner here. I made a macro that creates tables from datasets of different libraries (there is one library for each year). The proc tabulate part works fine. My problem is that I would like to export each table to one sheet within the same Excel file, and each sheet labeled with the macro variable "year". In the end I would like to have one Excel file with sheets named 20, 19, 18, 17, 16, 15 and 14.

I tried this by inserting an ods tagsets.excelxp command, as this is the export command that was also used throughout the rest of the syntax, which I "inherited". The result is an Excel file with a single sheet named "&year." (not "14") and the data from the last macro call, year 14.

Does anybody know how this can be solved? Also, I would be glad for suggestions concerning a more elegant way than calling the macro 7 times. Like looping over a list of years or something.

   %macro MyMacro (year = );
    %local indsn;
    %let indsn=path&year..myfile1;
    
ods tagsets.excelxp File='P:\Folder\Folder\Folder\MyMacro.xls'  style=statistical options(sheet_name='&year.'); 

    proc tabulate data=&indsn;
    class var1 var2;
    table var1 all, var2 all/printmiss;
    title "var1 and var2, year: &year.";
    run;

ods tagsets.excelxp close;
    
    %mend MyMacro;
    
    %MyMacro(year=20)
    %MyMacro(year=19)
    %MyMacro(year=18)
    %MyMacro(year=17)
    %MyMacro(year=16)
    %MyMacro(year=15)
    %MyMacro(year=14)

Why not just run a vba that combines them after?

Solar Mike
– Solar Mike

2022-03-31 07:42:59 +00:00
Commented Mar 31, 2022 at 7:42 — Solar Mike
– Solar Mike, Commented Mar 31, 2022 at 7:42

deristnochda · Accepted Answer · 2022-03-31 08:51:59Z

1

For this kind of stuff, you usually use the BY statement.

proc sort data=sashelp.cars out=work.cars;
  by origin;
run;

ods excel file="path/file.xlsx";

proc tabulate data=work.cars;
  by origin;
  class make type;
  table make all, type all / printmiss;
run;

ods excel close;

The ODS statement tells SAS the destination for the ODS output. It depends on your system and license, which excel export you can use. I went for the excel instead of excelxp since it generates an xlsx instead of the xls.

The BY statement groups the output in different tables and also writes the output on different excel sheets.

Also, the macro is not really needed. If the input datasets are properly named (e. g. basename_year) you can read all of them into one dataset using

data all_years;
  set basename_: indsname=dsn;
run;

and then use the dsn string to extract the year if it is not contained in each individual dataset.

answered Mar 31, 2022 at 8:51

deristnochda

5854 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

petit_dejeuner Over a year ago

Thank you very much for your reply! I understand that usually it's good to just match/join similar datasets and then do the calculations on the joint dataset. However, I want to avoid it in this case: Each of the datasets is very big already, even when working on a single data set some procedures takes some time, the screen freezes for a bit, etc. I really don't want to create such a huge dataset, with another set added each year. Also, the variables are not 100% identical in each year, so it would create additional effort to either streamline that or get rid of the unnecessary ones.

petit_dejeuner Over a year ago

So either way, I think the matching of datasets wouldn't save me time, effort or code lines overall. At the moment I have just written the ods tagsets.excelxp command for the 7 different years without a macro, with the years hard-coded. I was just interested whether it is possible to make a macro out of that.

Collectives™ on Stack Overflow

SAS Macro: Exporting tables to different sheets within one Excel file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related