All Charts in New Sheet on existing Excel File

[david27]

Hello,

I have the below code:

Problem is- It produces multiple Sheet- Chart, Chart 2.

I want all charts in the same sheet- Chart.

Also,

This excel file that i am writing out, already has a tab named- "Data". I don't want to disturb that. I want that as is.

data temp;
infile datalines missover dsd dlm=" ";
input yyyymm pct_change1 pct_change2;
informat yyyymm yymmdd10. pct_change1 pct_change2 percent10.2;
format yyyymm yymmdd10. pct_change1 pct_change2 percent10.2;
datalines;
2020-05-01 0.74% 0.52%
2020-06-01 0.84% 0.25%
2020-07-01 0.67% 0.62%
2020-08-01 0.52% 0.24%
2020-09-01 0.56% 0.34%
2020-10-01 0.50% 0.45%
2020-11-01 0.37% 0.65%
2020-12-01 1.07% 0.95%
2021-01-01 0.38% 0.25%
2021-02-01 0.34% 0.75%
2021-03-01 0.63% 0.55%
2021-04-01 0.80% 0.68%
2021-05-01 0.71% 0.28%
;run;

%let outfile=/path/on/unix/server/tempreport.xlsx;
ods excel file="&outfile.";
	ods excel options(
	Sheet_name= 'Chart'
	);

%macro charting(variable=,label=,LowerRefLine=,UpperRefLine=); 
proc sgplot data = temp ;
  series x = yyyymm y = &variable. ;
  refline 0 &LowerRefLine.  &UpperRefLine. / axis = y ;
  xaxis label="YearMonth";
  yaxis label="&label.";
run;
%mend charting;
%charting(variable=pct_change1,label=Label For 1);

%charting(variable=pct_change2,label=Label For 2);
ods excel close;

Please advise.

[Reeza]

Unfortunately I don't think ODS EXCEL supports writing to pre-existing Excel files.

[david27]

Any other alternative?

Template? GTL?

[ballardw]

Easiest for a one-time is to write to a different Excel file and then copy the sheet to the existing.

[Reeza]

---

@david27 wrote:

Any other alternative?

Template? GTL?

---

Depends on what's in that first sheet. If it's just a data table I'd import it and then re-write it out using ODS EXCEL so that the whole thing is one report. 

Or use VBS/VBA to combine Excel files after the fact.