Dealing with tabs in ODS EXCEL and PROC SGRENDER

[CHUNantes]

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.

[Cynthia_sas]

Hi:
Without data and all your code, including the template code, it is hard to make a constructive suggestion. I note that you have not shown all your code, only the %DO loop that generates output. I assume you are creating an XLSX file for ODS EXCEL but it would be useful to see ALL your code, including the beginning ODS statements with FILE= and any other suboptions you're setting.

If this is a very complex set of templates and data that you're using with SGRENDER, you might want to consider opening a track with Tech Support on this.

Cynthia

[Tom]

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;

[CHUNantes]

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;

[Cynthia_sas · Accepted solution]

Hi:
Then it really sounds like you need to open a track with Tech Support on this.
Cynthia

[CHUNantes]

OK, I will do it.

Thanks for your help.

[SR_FR]

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

[CHUNantes]

Hello / Bonjour.

I achieve to the same solution after SAS Support gave me the tip in my ticket 😉

Thanks / Merci

[SR_FR]

Bonjour

je me doutais bien que le support trouverait 😉