BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CHUNantes
Obsidian | Level 7

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.

 

January1.png

January2.png

February.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
Hi:
Then it really sounds like you need to open a track with Tech Support on this.
Cynthia

View solution in original post

8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
Super User Tom
Super User

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
Obsidian | Level 7

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
SAS Super FREQ
Hi:
Then it really sounds like you need to open a track with Tech Support on this.
Cynthia
CHUNantes
Obsidian | Level 7
OK, I will do it.

Thanks for your help.
SR_FR
Obsidian | Level 7

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
Obsidian | Level 7
Hello / Bonjour.

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

Thanks / Merci
SR_FR
Obsidian | Level 7

Bonjour

 

je me doutais bien que le support trouverait 😉

 

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1140 views
  • 2 likes
  • 4 in conversation