Problem using ODS EXCEL

[HermanoRocha]

ods graphics on/reset;
ods excel file="graficoshistogramas.xlsx" path="F:\REMOVED TO DEIDENTIFICATION\"
style=sty_custom;

proc sql noprint;
select name into :hist_state1- 
from dictionary.columns
where upper(libname)='WORK'
and upper(memname)='MERGE0'
and type='num';
quit;

%let nobs=&sqlobs;


%macro generate_histogram;

%do i=1 %to &nobs;

proc sgplot data=WORK.MERGE0;
title "Histograma de &&&hist_state&i";
histogram &&&hist_state&i;
run;quit;


%end;

%mend;

%generate_histogram;

120
121  ods graphics on/reset;
122  ods excel file="graficoshistogramas.xlsx"
122! path="F:\REMOVED TO DEIDENTIFICATION\"
     ----
     22
     76
ERROR 22-322: Syntax error, expecting one of the following: ;,
              ANCHOR, AUTHOR, BOX_SIZING, CATEGORY, CLOSE,
              COMMENTS, CSSSTYLE, DOM, DPI, FILE, GFOOTNOTE,
              GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE,
              NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE,
              SUBJECT, TEXT, TITLE, WORK.
ERROR 76-322: Syntax error, statement will be ignored.
123  style=sty_custom;
124
125  proc sql noprint;
126  select name into :hist_state1-
127  from dictionary.columns
128  where upper(libname)='WORK'
129  and upper(memname)='MERGE0'
130  and type='num';
131  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


132
133  %let nobs=&sqlobs;
134
135
136  %macro generate_histogram;
137
138  %do i=1 %to &nobs;
139
140  proc sgplot data=WORK.MERGE0;
141  title "Histograma de &&&hist_state&i";
142  histogram &&&hist_state&i;
143  run;quit;
144
145
146  %end;
147
148  %mend;
149
150  %generate_histogram;

NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.18 seconds
      cpu time            0.01 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.18 seconds
      cpu time            0.01 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.06 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.03 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.


NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           0.17 seconds
      cpu time            0.04 seconds

NOTE: There were 29 observations read from the data set
      WORK.MERGE0.

Hi everyone,

Hope you are doing well,

I'm not being able to use ODS Excel on my SAS. When I try, the graphs are correctly generated, but no excel file is generated, with no error messages displayed. I can use other ODS like rtf and pdf with no issue. One thing that I believe may be my problem is that the "excel" word entered after ODS does not get blue, while rtf and pdf after ODS become blue. I can even generate an excel file (with .xlsx) using ODS RTF, but it will not open.

I appreciate any suggestions,

Many thanks for considering my request.

Best wishes

[PaigeMiller]

Show us the code.

Show us the log.

Paste the code into the "code window" (little running man icon) and paste the log into the "log window" (</> icon just to the left of the little running man)

[HermanoRocha]

Sorry about that, I included it now. Thank you for your reply.

[PaigeMiller · Accepted solution]

You said there were no error messages, but ... 

Maxim 2: "Read the LOG"

122  ods excel file="graficoshistogramas.xlsx"
122! path="F:\REMOVED TO DEIDENTIFICATION\"
     ----
     22
     76
ERROR 22-322: Syntax error, expecting one of the following: ;,
              ANCHOR, AUTHOR, BOX_SIZING, CATEGORY, CLOSE,
              COMMENTS, CSSSTYLE, DOM, DPI, FILE, GFOOTNOTE,
              GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE,
              NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE,
              SUBJECT, TEXT, TITLE, WORK.
ERROR 76-322: Syntax error, statement will be ignored.
123  style=sty_custom;

There is no PATH= option for ODS EXCEL, the full path goes in FILE=

[HermanoRocha]

Thank you it worked! In fact it has not solved all of my problems (I would like to have editable graphs in excel, allowing, for example, to adjust the titles), but at least I exported it to excel. In case you know how to export editable graphs in excel, please let know. All the best,

[ballardw]

---

@HermanoRocha wrote:
Thank you it worked! In fact it did not solved all of my problems (I would like to have editable graphs in excel, allowing, for example, to adjust the titles), but at least I exported it to excel. In case you know how to export editable graphs in excel, please let know. All the best,

---

What kind of edits?

The Title statement will allow you to change fonts, colors and orientation of text in the middle of a single title. In fact it will do more that Excel is likely to allow for a title.

A small sample: (change the X to drive you want to use in the file path)

ods excel file="x:\title_examples.xlsx";

title1 "A basic title statement";
title2 j=l color=red "A bit more" j=r height=16pt color=blue "different title";
title3 font='MS UI Gothic' "and now a different font";

proc sgplot data=sashelp.class;
   scatter x=height y=weight/group=sex;
run;

ods excel close; 

[HermanoRocha]

Thank you for your reply! In fact, I could do the edits on SAS, the thing is that I normally share the analysis with my students and collaborators who can't use SAS and would like to adjust the graphs details themselves in Excel... So I was searching for a way to help them. For example, in SPSS you can paste the graphs into office software in an office editable fashion, so I was hoping SAS could also do it.

[Cynthia_sas]

Hi:

 Run a simple test without any graphs:

ods excel file='c:\temp\class.xlsx';
proc report data=sashelp.class;
run;
ods excel close;

  Note that the color-coding in SAS is not always updated for every version. When I use SAS Display Manager, the token "excel" in "ods excel file=..." does not get color coded, but my code works. Without a graph, does the above code created a class.xlsx file in the c:\temp location? If so, then move on  and try this code:

ods excel file='c:\temp\class_graf.xlsx';
proc report data=sashelp.class;
run;

proc sgplot data=sashelp.class;
scatter x=height y=weight / group=age;
run;
ods excel close;

  When I run that code, I get the PROC REPORT output on one sheet and the graph from SGPLOT on a second sheet?

 If the above code works for you, but your code does not work, then either post your code and some data here or else open a case with SAS Tech Support so they can look at all of your code and your data to try to figure out why your code is not working.

  Assuming you have the right version of SAS and that you also have the SAS/Access for PC Files component, ODS EXCEL should work with versions of SAS from 9.4 M3 and higher.

Cynthia

[HermanoRocha]

Thank you for your reply, the problem was that I was using a path option, sorry for not letting you see the log before. All the best,

[ballardw]

---

@HermanoRocha wrote:
Hi everyone,

Hope you are doing well,

I'm not being able to use ODS Excel on my SAS. When I try, the graphs are correctly generated, but no excel file is generated, with no error messages displayed. I can use other ODS like rtf and pdf with no issue. One thing that I believe may be my problem is that the "excel" word entered after ODS does not get blue, while rtf and pdf after ODS become blue. I can even generate an excel file (with .xlsx) using ODS RTF, but it will not open.

I appreciate any suggestions,

Many thanks for considering my request.

Best wishes

---

---

---

Editor highlighting is not definitive for whether something will "work" or not. It may just indicate that a specific word has not been added to a highlight list. The LOG tells you whether the word was a problem.

Besides the not legal for ODS EXCEL option of PATH= you are missing a critical piece for creating ODS destination files: A CLOSE statement.

Until the Close is executed SAS has not actually been told to finish the file creation processs.

So any thing creating ODS destination output such as Excel, RTF, PDF or WORD will look like

ODS destination <destination options>;

< Procedures that generate output, graphic or tabular>

ODS destination CLOSE;

As far as ODS RTF opening in Excel I would put a small stack of $ in a bet that Excel gave some sort of message that the file format was not expected and refused to open it. For years many people using a number of file format lied to Excel by naming the file with an XLS and later XLSX file so Excel would attempt to open the file. Eventually Microsoft added code to check the file format to see if it was actually in the expected format and refuses to open them.

[Kurt_Bremser]

, with no error messages displayed

You need to see an opthalmologist:

122  ods excel file="graficoshistogramas.xlsx"
122! path="F:\REMOVED TO DEIDENTIFICATION\"
     ----
     22
     76
ERROR 22-322: Syntax error, expecting one of the following: ;,
              ANCHOR, AUTHOR, BOX_SIZING, CATEGORY, CLOSE,
              COMMENTS, CSSSTYLE, DOM, DPI, FILE, GFOOTNOTE,
              GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE,
              NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE,
              SUBJECT, TEXT, TITLE, WORK.
ERROR 76-322: Syntax error, statement will be ignored.

This IS an ERROR message, if I ever saw one.