Hello.
I'm trying to port an Excel report to SAS ODS Excel and I'm very close to archeive it.
I'm just facing a small issue.
I'm using a macro to produce some graphics in a tab as I need to make up to 12 tabs (one per month).
The result is OK for all months except the first one.
For the first tab, the first graphic is OK and then SAS create a new tab, called JANUARY2, with the 2 other graphics. For other tabs, the 3 graphics are in the same tab.
Here is the macro I wrote :
%MACRO graphs_mois(nb_mois);
%LET mois1=JANUARY;
%LET mois2=FEBRUARY;
%LET mois3=MARCH;
%DO i=1 %TO &nb_mois;
* Onglet *;
ODS EXCEL OPTIONS(SHEET_NAME="&&mois&i.." SHEET_INTERVAL="NOW" EMBEDDED_TITLES="YES");
TITLE;
/* Generate the chart */
proc sgrender data=swork.graphiques(WHERE=(MONTH(Date)=&i)) template=passages;
run;
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
/* Generate the chart */
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_hospit;
run;
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
/* Generate the chart */
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_age;
run;
%END;
%MEND;
%graphs_mois(3);
Is there any reason why the first tab is split in two ?
Regards.
Perhaps you just need to set the sheet interval to NONE before the first call to the proc.
/* Generate the 1st chart */
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=swork.graphiques(WHERE=(MONTH(Date)=&i)) template=passages;
run;
/* Generate the 2nd chart */
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_hospit;
run;
/* Generate the 3rd chart */
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_age;
run;
Thanks all for your help.
@Tom : Unfortunatly, with your code, I only have one tab with all the graphics.
@Cynthia_sas : Here is my complete progam and my data (CPORT file in attachment). I dont think my issue is related to SGRENDER complexity because even after removing the more complex SGRENDER, the first tab is always split in two.
* Graphics use full width of the page *;
OPTIONS PAPERSIZE=(50cm 21cm) MISSING=" ";
ODS _ALL_ CLOSE;
ODS GRAPHICS / RESET WIDTH=1600px HEIGHT=220px;
ODS EXCEL FILE="C:\temp\StatUrg.xlsx";
* Define the line chart template *;
proc template;
define statgraph passages;
begingraph;
layout lattice / rows = 1 columns = 2;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=valeur_a / name="Adultes"
/* Group by stock to draw a line for each stock */
group=type_a
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8);
discretelegend "Adultes" / HALIGN=CENTER VALIGN=TOP;
endlayout;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=valeur_e / name="Enfants"
/* Group by stock to draw a line for each stock */
group=type_e
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8);
discretelegend "Enfants" / HALIGN=CENTER VALIGN=TOP;
endlayout;
endlayout;
endgraph;
end;
run;
proc template;
define statgraph pct_hospit;
begingraph;
layout lattice / rows = 1 columns = 2;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=Rap_HospA / name="Adultes"
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8)
LEGENDLABEL='%tage Hospit / Nbre passages' ;
discretelegend "Adultes" / HALIGN=CENTER VALIGN=TOP;
endlayout;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=Rap_HospE / name="Enfants"
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8)
LEGENDLABEL='%tage Hospit / Nbre passages' ;
discretelegend "Enfants" / HALIGN=CENTER VALIGN=TOP;
endlayout;
endlayout;
endgraph;
end;
run;
proc template;
define statgraph pct_age;
begingraph;
layout lattice / rows = 1 columns = 2;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=Rap_75 / name="Adultes"
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8)
LEGENDLABEL='%tage > 75 ans / Nbre passages' ;
discretelegend "Adultes" / HALIGN=CENTER VALIGN=TOP;
endlayout;
layout overlay /
/* Add a grid */
xaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) timeopts=(interval=day) gridattrs=(pattern=dot color=lightgray))
yaxisopts=(DISPLAY=(LINE TICKS TICKVALUES) griddisplay=on gridattrs=(pattern=dot color=lightgray));
/* Generate the line chart */
linechart category=date response=Rap_BB1 / name="Enfants"
/* Set the baseline at 0 */
baselineintercept=0
/* Display the lines and fill */
display=(line fill)
/* Specify the line attributes */
lineattrs=(thickness=2)
/* Specify the fill attributes */
fillattrs=(transparency=0.8)
LEGENDLABEL='%tage < 1 an / Nbre passages' ;
discretelegend "Enfants" / HALIGN=CENTER VALIGN=TOP;
endlayout;
endlayout;
endgraph;
end;
run;
%MACRO graphs_mois(nb_mois);
%LET mois1=JANUARY;
%LET mois2=FEBRUARY;
%LET mois3=MARCH;
%DO i=1 %TO &nb_mois;
* Tab *;
ODS EXCEL OPTIONS(SHEET_NAME="&&mois&i.." SHEET_INTERVAL="NOW" EMBEDDED_TITLES="YES");
TITLE;
/* Generate the chart */
proc sgrender data=swork.graphiques(WHERE=(MONTH(Date)=&i)) template=passages;
run;
/* Generate the chart */
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_hospit;
run;
/* Generate the chart */
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=swork.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_age;
run;
%END;
%MEND;
%graphs_mois(3);
ODS EXCEL CLOSE;
Hi
a little re-organization of your program helps:
%MACRO graphs_mois(nb_mois);
%LET mois1=JANUARY;
%LET mois2=FEBRUARY;
%LET mois3=MARCH;
ods excel file='toto.xlsx';
ods excel options (SHEET_INTERVAL="NONE");
TITLE;
%DO i=1 %TO &nb_mois;
ODS EXCEL OPTIONS(SHEET_NAME="&&mois&i.." EMBEDDED_TITLES="YES");
proc sgrender data=work.stat_urg(WHERE=(MONTH(Date)=&i)) template=passages;
run;
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=work.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_hospit;
run;
ODS EXCEL OPTIONS(SHEET_INTERVAL="NONE");
proc sgrender data=work.stat_urg(WHERE=(MONTH(Date)=&i)) template=pct_age;
run;
ods excel options(SHEET_INTERVAL="NOW");
%END;
ods excel close;
%MEND;
%graphs_mois(3);
with SAS 9.4M6, I obtain something that looks like what you want (see the toto.xlsx file attached)
Best regards
Sébastien
Bonjour
je me doutais bien que le support trouverait 😉
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: