topic Re: All Charts in New Sheet on existing Excel File in Graphics Programming

All Charts in New Sheet on existing Excel File

david27 — Thu, 12 Aug 2021 20:25:46 GMT

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.

Re: All Charts in New Sheet on existing Excel File

Reeza — Thu, 12 Aug 2021 20:44:19 GMT

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

Re: All Charts in New Sheet on existing Excel File

david27 — Thu, 12 Aug 2021 20:45:34 GMT

Any other alternative?

Template? GTL?

Re: All Charts in New Sheet on existing Excel File

ballardw — Thu, 12 Aug 2021 20:47:13 GMT

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

Re: All Charts in New Sheet on existing Excel File

Reeza — Thu, 12 Aug 2021 21:12:03 GMT

@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.