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
So is there any way to doing this? or a VBA Macro? I prefer doing it in SAS. Any help or hint would appreciated.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.