topic Re: proc report by group and single title in SAS Programming

proc report by group and single title

LukeL — Wed, 21 Jul 2021 08:17:29 GMT

Hi all, I have a problem with proc report.

I'm writing an excel (tagset.excelxp, on 9.4M1) and have on a same sheet three tables generated by a proc report with a by.

I would like to have just one title for those tables, but the report with the by group write it for each one (either with embed_titles* options enabled.

Is there a way to tell the proc report to write titles only once, or the only way is to write each table manually?

Thanks 🙂

Re: proc report by group and single title

ChrisNZ — Wed, 21 Jul 2021 08:23:29 GMT

Could you write some ods text and remove the titles?

Re: proc report by group and single title

LukeL — Wed, 21 Jul 2021 08:30:22 GMT

Hi, as far as I know, excelxp does not support ods text, isn't it?

Also, a way to force the title output would be good, since I could put the title, force it, then delete the title and make the report...

Re: proc report by group and single title

andreas_lds — Wed, 21 Jul 2021 10:40:43 GMT

With tagset.excelxp you don't create excel-files but xml-files that can be read by excel. Unfortunately switching to ods excel is no option, because you are using an old version of 9.4, upgrading to at least m5 is highly recommended.

You may want to post the code you are using, would make it so much easier to suggest something useful.

Re: proc report by group and single title

LukeL — Wed, 21 Jul 2021 10:47:39 GMT

I'm trying to convince customer to update the M1 since almost 4 years...but still here 😞

The code is quite simple

proc sort data=sashelp.class out=TEST;
    by SEX;
run;

title "Some title referred to M and F";

proc report data=TEST missing;  
    by SEX;
    columns NAME AGE;
    define NAME / group;
    define AGE / mean;
run;

Same code is used to produce an xml/xls or a pdf, so the solution should be not tightened to the output. For now just write some macro code that get all the by group value and itearate over it...

Re: proc report by group and single title

Cynthia_sas — Wed, 21 Jul 2021 18:20:02 GMT

Hi:

TAGSETS.EXCELXP does have an Embed_Titles_Once suboption, but it did not work for me with your PROC REPORT. This Tech Support note explains that the option is problematic: https://support.sas.com/kb/46/620.html .

However, when I tried Embed_Titles_Once with ODS EXCEL, that suboption worked for me with a version of your code. I created a subset of the sorted data to make everything fit in one screen shot.

I wasn't sure what you wanted, but asking for the mean AGE for every name didn't make sense, unless you wanted to see the Overall Mean at the bottom of the list for each group.

Cynthia