The SAS Output Delivery System and reporting techniques

ods excel tagsets - center horizontal

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

ods excel tagsets - center horizontal

%let ausgabedatei= test;

ods listing close;
Options papersize=A4  TopMargin = 0.984251969in LeftMargin = 0.1in RightMargin = 0.1in; run;
Ods Tagsets.ExcelXP style=styles.test file="&pfad&ausgabedatei..xml" 
options(DPI='600' sheet_interval='none' merge_titles_footnotes='yes' gridlines='yes' autofit_height='yes' SKIP_SPACE='0,0,0,0,0,0,0'
                   Center_Vertical= 'yes' Center_Horizontal= 'yes' gridlines='no'
                   embedded_titles='yes' embedded_footnotes='yes' sheet_interval='none' sheet_name='II.A.1');
ods escapechar='^';
title1 wrap font= 'arial' bold "II.A.1 Titel1
^{newline}Subtitle in full length";
footnote1 justify=left  "_________";
footnote2 J=left height=7pt "1) Note1.
^{newline}note2.";

proc tabulate data=sashelp.cars missing out=out;
class make model type;
table make model, type*n='' /box='Cars' printmiss misstext='X';
run;

When using above code, the Option for Center hrizontally does not work.

Ideas how to fix it?


Accepted Solutions
Solution
‎07-16-2015 10:11 AM
SAS Super FREQ
Posts: 8,820

Re: ods excel tagsets - center horizontal

Hi:

  As I explained in my previous posting, there isn't an equivalent to this in TABULATE (where you have a row for each MAKE first, followed by a row for each MODEL):

table make model,

         type*n='' /box='Cars' printmiss misstext='X';

in PROC REPORT. Centering the header cell for CARS will be the same in either REPORT or TABULATE, just changes a bit. In TABULATE, you add the style controls to your BOX= option:

        

table make model,

         type*n='' /box={label='Cars' style={just=c vjust=m}} printmiss misstext='X';
    

JUST= controls the horizontal justification (L,R,C) and VJUST= controls the vertical alignment (T, B, M). You can do the same thing in a DEFINE statement in PROC REPORT (but there isn't a BOX area), so you'd have to either use a spanning header in PROC REPORT as shown below, or you'd have to put the controls in a DEFINE statement:

define make /group style(Header)={just=c vjust=m} 'Cars';
      

  But, PROC REPORT does NOT have a BOX area like TABULATE. In addition, as I said, without some extra data manipulation, you can't actually "stack" MAKE and MODEL in the same column in PROC REPORT. For example, in TABULATE, the "row header area" is under the BOX area and the "column header area" is to the right of the box area. In your row header area, you have summary information for 2 different variables: MAKE and MODEL. PROC REPORT won't do that. So just taking the OUT= dataset from TABULATE will not work in PROC REPORT to give you what you want, as you've discovered.
      

  Can you explain why the TABULATE output won't work in your report. What is leading you to want to use PROC REPORT for the results?? For example, using a subset of SASHELP.CARS, this PROC REPORT output shows you how REPORT can automatically "nest" each MODEL within the MAKE. Regarding  centering, note that the spanning header for Cars is automatically centered in the cell.

    

Cynthia

   

options missing='X';
ods html file='c:\temp\altreport.html';
ods tagsets.excelxp file='c:\temp\altreport.xml' style=htmlblue;

  proc report data=sashelp.cars missing;
  where make in ('Acura', 'BMW', 'Buick');
    column ('Cars' make model) n,type;
    define make / group style(column)=Header;
    define model / group style(column)=Header;
    define n / ' ';
    define type / across;
  run;
ods _all_ close;
title; footnote;
options missing=.;

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: ods excel tagsets - center horizontal

You are missing a lot of the code, paste just text in for the code, and expand on your description "inserting SAS-code" where, and what sas code?  Personally I would create a dataset from the proc tabulate and then use proc report to output the data. 

Occasional Contributor
Posts: 18

Re: ods excel tagsets - center horizontal

I might not be as familiar with proc report as neccessary, but how do i handle missing values there ( i want them as x) and how do I get the same output as in tabulate?

in short, can you Show me a proc report that has similar output as the proc tabulate?

(I tried to past the code as text, but there are words missing)

Super User
Posts: 9,875

Re: ods excel tagsets - center horizontal

Post a picture and a sample data which can explain your Q better.

Super User
Super User
Posts: 7,720

Re: ods excel tagsets - center horizontal

You deal with missing's in exactly the same way:

options missing=x;

As for proc report output looking like tabulate, you save the dataset from tabulate, so in your case, you have out=out.  Then you can use this simply like:

proc report data=out ...;

     columns _all_;

run;

or you can go further and add different labels, styling layout etc.  There is a lot of "sas proc report examples" papers out there which will cover most of what you want to do, here is one for example:

http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

Occasional Contributor
Posts: 18

Re: ods excel tagsets - center horizontal

No, it is not the same.

proc Report gives:

MakeModelTypeType of ObservationPage for ObservationTable for ObservationN
AcuraHybrid10111.
AcuraSUV101111
AcuraSedan10111

while tabulate produces:

CarsType
HybridSUVSedanSportsTruckWagon
MakeX  1  5  1XX
Acura
AudiXX
  13

  4
X
  2
Super User
Super User
Posts: 7,720

Re: ods excel tagsets - center horizontal

Thats odd.  But then I don't use tabulate, so could be right.  Anyways, just transpose the out dataset:

proc transpose data=out out=t_out;

     by make;

     var n;

     id type;

     idlabel type;

run;

Then proc report that.  Basically, get the data as you want to see it reported, then report it.

SAS Super FREQ
Posts: 8,820

Re: ods excel tagsets - center horizontal

Hi, first, it looks like you are using the OUT= option with TABULATE and then passing the WORK. dataset from OUT= to PROC REPORT. Is that correct? When TABULATE makes an OUTPUT dataset, it does NOT maintain the same structure as the report table. The output from a TABULATE OUT= is a "flattened" version of the table. If you want the TABULATE output in Excel using TAGSETS.EXCELXP, why not just use TABULATE????

  However, to answer your question about the CENTERING sub-options. You do NOT see the CENTERING in the worksheet. You have to actually PRINT or go into PRINT PREVIEW mode to see the centering. When I run a version of your code, I do see the centering both vertically and horizontally in PRINT PREVIEW mode.

  There is no equivalent option for PROC REPORT such as "MISSTEXT=" -- that is a TABULATE option. You can use OPTIONS MISSING='X'; before your PROC REPORT step and that might do what you want. But PROC REPORT will not give you the same structure as TABULATE from the OUT= dataset, which is what it seems you want. However, you didn't post any PROC REPORT code, so I'm not sure what you want.

cynthia

Super User
Super User
Posts: 7,720

Re: ods excel tagsets - center horizontal

Sorry Cynthia, thats was me pushing for the proc report part as I don't like or use tabulate.  May I ask why you don't see centering in a cell from the tabulate procedure, as proc report seems to honor it?

SAS Super FREQ
Posts: 8,820

Re: ods excel tagsets - center horizontal

Hi: I don't understand. Center_Vertical and Center_Horizontal apply to the whole sheet. Not to the individual cells. So when you ask why you don't see centering in a cell from tabulate vs report, can you be more specific???

  As for pushing for the PROC REPORT part -- there's no equivalent in REPORT for what the OP shows in TABULATE. The OP had a table for MAKE immediately followed by a table for MODEL with both MAKE and MODEL in the row dimension and then TYPE nested with N in the column dimension:

table make model,

         type*n='' /box='Cars' printmiss misstext='X';

  There's no equivalent for this type of "stacking" in the row area with REPORT unless you do some data manipulation to make a "helper variable" -- that's one of the reasons that folks use TABULATE -- because they want to do stuff like stacking tables in the row (or column) dimension. I can make a table in REPORT look like the original output from TABULATE, but I don't know why you'd do that. If the look and feel of TABULATE with multiple tables in the row dimension is what's needed, then just use TABULATE with EXCELXP.

  As for centering...I guess I'm not sure what you mean -- data cells, header cells or entire tables in the worksheet.

    cynthia

Occasional Contributor
Posts: 18

Re: ods excel tagsets - center horizontal

Hi Cynthia,

I want the header cell with "cars" to be centered. both horizontally and vertically.

And I want to use proc Report.

Thank you

Christa

Solution
‎07-16-2015 10:11 AM
SAS Super FREQ
Posts: 8,820

Re: ods excel tagsets - center horizontal

Hi:

  As I explained in my previous posting, there isn't an equivalent to this in TABULATE (where you have a row for each MAKE first, followed by a row for each MODEL):

table make model,

         type*n='' /box='Cars' printmiss misstext='X';

in PROC REPORT. Centering the header cell for CARS will be the same in either REPORT or TABULATE, just changes a bit. In TABULATE, you add the style controls to your BOX= option:

        

table make model,

         type*n='' /box={label='Cars' style={just=c vjust=m}} printmiss misstext='X';
    

JUST= controls the horizontal justification (L,R,C) and VJUST= controls the vertical alignment (T, B, M). You can do the same thing in a DEFINE statement in PROC REPORT (but there isn't a BOX area), so you'd have to either use a spanning header in PROC REPORT as shown below, or you'd have to put the controls in a DEFINE statement:

define make /group style(Header)={just=c vjust=m} 'Cars';
      

  But, PROC REPORT does NOT have a BOX area like TABULATE. In addition, as I said, without some extra data manipulation, you can't actually "stack" MAKE and MODEL in the same column in PROC REPORT. For example, in TABULATE, the "row header area" is under the BOX area and the "column header area" is to the right of the box area. In your row header area, you have summary information for 2 different variables: MAKE and MODEL. PROC REPORT won't do that. So just taking the OUT= dataset from TABULATE will not work in PROC REPORT to give you what you want, as you've discovered.
      

  Can you explain why the TABULATE output won't work in your report. What is leading you to want to use PROC REPORT for the results?? For example, using a subset of SASHELP.CARS, this PROC REPORT output shows you how REPORT can automatically "nest" each MODEL within the MAKE. Regarding  centering, note that the spanning header for Cars is automatically centered in the cell.

    

Cynthia

   

options missing='X';
ods html file='c:\temp\altreport.html';
ods tagsets.excelxp file='c:\temp\altreport.xml' style=htmlblue;

  proc report data=sashelp.cars missing;
  where make in ('Acura', 'BMW', 'Buick');
    column ('Cars' make model) n,type;
    define make / group style(column)=Header;
    define model / group style(column)=Header;
    define n / ' ';
    define type / across;
  run;
ods _all_ close;
title; footnote;
options missing=.;

Occasional Contributor
Posts: 18

Re: ods excel tagsets - center horizontal

Sorry Cynthia,

I wanted to say that I do not use proc Report. That was just a Suggestion from RW9.

Thanks a lot for your code. It works perfectly for me.

Just one more question: can I use vjust in the title Statement? I tried

title1 wrap font= 'arial' bold j=c vjust=m "II.A.1 Entwicklung der Vollzeitäquivalente der Beschäftigten des öffentlichen Dienstes in Hessen

^{newline}nach Beschäftigungsbereichen, Art der Beschäftigungsverhältnisse und Geschlecht";


which gives me an error.

Christa

SAS Super FREQ
Posts: 8,820

Re: ods excel tagsets - center horizontal

Hi, Christa:

  I'm not sure why you would want to use vjust in the Title. The title should work to be automatically centered both vertically and horizontally. Also, I don't know why you are using both WRAP and ESCAPECHAR {newline}. Neither VJUST nor WRAP are valid options to go into the TITLE statement. You can see all the valid options here: SAS(R) 9.4 Statements: Reference, Fourth Edition

  But I believe the error in your TITLE statement will go away if you get rid of VJUST and WRAP.

cynthia

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1150 views
  • 1 like
  • 4 in conversation