Conditional ods file and output title names

[haydn89]

Hello everyone,

I want to know the best way to create a series of reports with conditional output and title names, preferably in relation to the order in which the program is run.

I have a series of SAS programs, and each program uses different input data.

I use the excelxp tagset to export a proc tabulate from my SAS programs into an xml file. I then use excel VBA to merge all the xml files into one workbook. One problem that often arises is when I am asked to re-arrange the tables in my report. I try to make sure the SAS programs reflect the order of the output and title names in my report for reference later on.

For example, let’s say that I have three programs, called table1.sas, table2.sas and table3.sas. I specify the file name in my output statement as table1.xml, table2.xml and table3.xml, and the title names in my tabulate procedures are called Table 1, Table2 and Table 3 respectively.

Sometimes, after creating and running the programs, I am asked to make table3.sas into table1.sas. Consequently, I have to go back and re-name ALL the SAS programs (i.e. table3.sas becomes table1.sas, table1.sas becomes table2.sas and table2.sas becomes table3.sas), and similarly, rename the filenames and title names.

This can become very time consuming, especially when I have over 50 tables. My current thought is that I might find some use from ODS Document (which I have just discovered), but if someone has a better idea, then I am all ears.

My workplace is currently running SAS 9.1.3 on a UNIX platform. We are currently in the process of upgrading to SAS Enterprise Guide 6.

Any assistance would be greatly appreciated.

Kind regards,

Haydn

[Reeza]

Why do you create multiple files and merge them and not create one file initially?

Looking into ODS Document and GREPLAY is a great option.

My "lazy way"

This is a case where it may be worth creating each table as macro, that took the table/title name. 

Then call them in the order you need to create the excel file.

ods tagsets.excelxp file="C:\temp\test.xml" style=meadow

options (sheet_interval='proc');

%macro(table1, title1);

%macro(table2, title2);

ods tagsets.excelxp close;

[haydn89]

Thank you Reeza,

That is a great approach!

I create multiple sas jobs and files to make my code more manageable (i.e. read and debug).

[ballardw]

Debug the output and then use %include if you want separate SAS code files;

ods tagsets.excelxp file="C:\temp\test.xml" style=meadow

options (sheet_interval='proc');

%include "C:\dummy sas code folder\sas program file 1.sas";

%include "C:\dummy sas code folder\sas program file 2.sas";

%include "C:\another dummy sas code folder\sas program file 3.sas";

ods tagsets.excelxp close;

Typically I organize programs so that all of the data sets are built outside off and ODS destination/ close block and just have the actual output generators within. So my approach above would include calls to the data generators before the ODS statement.

[haydn89]

Thank you Ballardw,

Using your approach I would have my data steps, and some format procedures, laid inside my call to the ods destination.

I guess I could reorient my sas jobs to get around this.

Cheers

[ballardw]

There's nothing wrong with having the data and output together, it is a style choice. The main reason I separate the two is for when I'm spending that other 90% of the time getting the output to look as needed. If there is any significant time involved in rebuilding identical data then it avoids that performance issue, especially during testing cycles. Also with mostly output procs grouped together it is easier to see where related changes need to be done.