topic ODS tagsets.excelxp in SAS Programming

ODS tagsets.excelxp

Ronein — Wed, 16 May 2018 07:54:26 GMT

Hello

In the insurance company where I work there are raw data tables that publish every month.

(For example:Raw_1711 is for NOV 2017)

Every month I need to use specific raw data files(determine be manager) and perform some summary statistics on them.

Then I need to create an output summary report(one table) that contain information about the requested tables.

My manager ask me to automat export the summary tables +raw data tables to one excel file in multiple sheets.

My task is to create a flexible program that know to export it .

I have a problem with ODS tagsets.excelxp step .

Please see the tables below:

Data Raw_1711;
input x y;
cards;
1 10.1
2 20
3 30
;
Run;
Data Raw_1712;
input x y;
cards;
4 40
5 50
6 60
;
Run;
Data Raw_1801;
input x y;
cards;
7 70
8 80
9 90
;
Run;
Data Raw_1802;
input x y;
cards;
10 100
11 110
12 120
;
Run;
Data Raw_1803;
input x y;
cards;
13 130
14 140
15 150
;
Run;
Data Raw_1804;
input x y;
cards;
16 160
17 170.1
18 180.2
;
Run;
Data Raw_1805;
input x y;
cards;
19 190
20 200.3
21 210
;
Run;

%let vector=1804+1803+1712;
%let k=3;
%put &vector.;

%macro Macro1;
%DO i=1 %TO &k.;
mon&i.=%scan(&vector.,&i.,+);
Call Symput("mon&i.",mon&i.);
%end;
%mend;

Data _null_;
%Macro1;
Run;
%put &mon1.;
%put &mon2.;
%put &mon3.;

%macro Macro2;
%DO i=1 %TO &k.;
%let mon=%scan(&vector.,&i.,+);
Data Mydata_&mon.;
SET Raw_&mon.(where=(int(y)=y));
Run;
%end;
%mend;
%Macro2;

%macro Macro3;
%DO i=1 %TO &k.;
%let mon=%scan(&vector.,&i.,+);
PROC SQL;
create table Summary&mon. as
select sum(y) as Sum_Y,
min(y) as Min_Y,
max(y) as Max_Y
from Mydata_&mon.
;
QUIT;
%end;
%mend;
%Macro3;

%macro Macro4;
%DO i=1 %TO &k.;
%let mon=%scan(&vector.,&i.,+);
proc transpose data=Summary&mon.
prefix=Y_&mon.
out=Summary2_&mon.;
run;
%end;
%mend;
%Macro4;

%macro Macro5;
%DO i=1 %TO &k.;
%let mon=%scan(&vector.,&i.,+);
Summary2_&mon.
%end;
%mend;

Data FinalOutput;
Merge %Macro5;
Run;

/* My target is to use ODS tagsets.excelxp to export folloeing tables to excel*/
/*I want to have a flexible code that foe each user arguments in vector parameter
we will get automatic export to excel .Each table in a separate sheeet*/
proc print data=FinalOutput noobs;Run;
proc print data=Mydata_1804 noobs;Run;
proc print data=Mydata_1803 noobs;Run;
proc print data=Mydata_1712 noobs;Run;

Re: ODS tagsets.excelxp

RW9 — Wed, 16 May 2018 08:10:47 GMT

Do you know what, there is at least 2 "better" methods of doing that which jump to mind without even looking at the code throughly.

1) Do you have SAS 9.4 (think that is the first)? If so then it is far simpler as you can use the libname step:

libname tmp xlsx "<path to file>/<filename>/xlsx";

proc copy in=work out=tmp;
  select raw_1711 raw_1712...;
run;

libname tmp clear;

Notice once again how I manage to use the code window - its the {i} above the post area, so that code clearly stands out and retains the code formatting (and I don't use mixed case, and use indents etc.).

2) Put all the data you want to export into one directory/libname, then you can use the SAS metadata to generate your code quickly and simply without all the messy macro code which will fall over most of the time. E.g. I put all the output data in a libname tmp.

ods tagsets.excelxp file="<path>/<filename>.xlsx";

data _null_;
  set sashelp.vtable (where=(libname="TMP"));
  call execute('ods tagsets.excelxp options(sheet_name="'||strip(memname)||'");
                proc report data=tmp.'||strip(memname)||' nowd;  column _all_; run;');
run;

ods tagsets.excelxp close;

Now this uses a magically thing called datasteps and Base SAS programming. The data _nul_ retrieves all the dataset names from the metadata in the given library and then for each of those, it creates a tab naming step and a proc report to output the data. In this way you do not need looping, name lists, number of datasets etc. Far simpler and more robust. You could do the same with proc export rather than tagsets.

Re: ODS tagsets.excelxp

Kurt_Bremser — Wed, 16 May 2018 08:55:36 GMT

As a first step, use a data step to determine the dataset names needed, and store them with call symput into a single macro variable.

Then concatenate those datasets into one, using the indsname option to retrieve the originating dataset name that you keep in a new variable in the dataset. (see example 12 in SET Statement)

From then on, you can use that variable for by-group processing in data/proc steps and for group by in SQL. NO further macro processing needed.

In ODS, use the appropriate option to switch sheets along the group variable.