Looking for help

JackZhang — Mon, 16 Mar 2015 18:37:05 GMT

Hi,All

I need some help for my project. I create SAS output to my excel with one tab, but i need create another tab using excel to link previous tab. So how I can keep the second tab when I run SAS code? Because every time I run the code, it will overwrite the workbook.

This is my code for the first tab,

**export excel file**;

ODS TAGSETS.excelxp FILE="\\cnpl.enbridge.com\Common\Cgy\Development\System Development\Facility\17 - System Analytics\Working Files\Jack\Pressure Cycling\Final.xls"

STYLE=Printer

OPTIONS ( Orientation = 'landscape'

FitToPage = 'yes'

sheet_name = 'L6LK'

panelcols='2'

Pages_FitWidth = '1'

Pages_FitHeight = '100'

embedded_titles = 'yes'

sheet_interval='none');

proc print data=Count_Summary noobs;

label COUNT = 'Count';

var Train Count;

sum Count;

title 'L6LK Train Count Up To Today';

run;

title "&G_Line Violation Report";

proc report data=FINAL nowd center;

columns

SHIPPER_BATCH_NAME

_&DESTINATION

FINAL_FCLTY_SHORT_NAME

Status

Total_Violation;

define _all_/display center;

compute _&DESTINATION;

* Placeholder for cell highlighting;

if (_&DESTINATION eq 'LK')

then call define( '_LK', 'style', 'style=[background=silver]');

endcomp;

compute FINAL_FCLTY_SHORT_NAME;

if (FINAL_FCLTY_SHORT_NAME eq 'PDVMWLT')

then call define('FINAL_FCLTY_SHORT_NAME', 'style', 'style=[background=red]');

else if (FINAL_FCLTY_SHORT_NAME eq 'ENBMUSTANG')

then call define('FINAL_FCLTY_SHORT_NAME', 'style', 'style=[background=green]');

else if (FINAL_FCLTY_SHORT_NAME eq 'TEXTRAD')

then call define('FINAL_FCLTY_SHORT_NAME', 'style', 'style=[background=pink]');

endcomp;

run;

ods tagsets.excelxp close;

So I create first tab with two tables, but I need to create second tab in excel to summarize the information from first tab, where i create using SAS.

I believe I need add more code on this. Please help.

Thank you

Jack

Re: Looking for help

JackZhang — Mon, 16 Mar 2015 19:01:56 GMT

So is there any way to doing this? or a VBA Macro? I prefer doing it in SAS. Any help or hint would appreciated.

Re: Looking for help

Steelers_In_DC — Mon, 16 Mar 2015 20:25:29 GMT

Jack,

Use this as a template. It should work for you:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

topic Re: Looking for help in ODS and Base Reporting

Looking for help

Re: Looking for help

Re: Looking for help