The SAS Output Delivery System and reporting techniques

Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 142
Accepted Solution

Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi all-

I am using ODS  Tagsets Excelxp and Proc SQL to produce multisheet XML workbooks. I don’t have any problem producing the workbooks, what I am having trouble with is formatting the spreadsheets. I don’t know how to write code for that.

  • For the whole workbook the font needs to change.

  • The font size of the header needs to be larger.

  • Add frozen headers

  • All of the rows need to be a certain size. 

Below I have an example of one spreadsheet and  I’ve written descriptions of how I would like to format the columns.

Any assistance will be greatly appreciated. Thanks do much!!!!!

Matt

ods listing close;

ods tagsets.excelxp file="X:\ORE\ScorecardDatabase\Final Raw Data.xls" style=minimal

options(sheet_interval='none' sheet_name="DATA1")

ods noptitle;

" " as d1 " Review Of CPS History",  /*the font size of this needs to change, column with must be changed and the whole column must be shaded in a color and center justified /*

cra1 "Q1",  /*column width must be changed and center justified*/

cra2 "Q2",  /*column width must be changed and center justified*/

cra3 "Q3", /*column width must be changed and center justified */

cra4 "Q4",  /*column width must be changed and center justified*/

" " as d2 "Program Choice", /*the font size of this needs to change, column with must be changed and the whole column must be shaded in a color and center justified /*

cra5 "Q5", /*column width must be changed and center justified */

&list5a, /*this produces a list of questions from a dictionary table – all question columns need to be formatted  the same*/

cra6 "Q6", /*column width must be changed and center justified */

cra7 "Q7",  /*column width must be changed and center justified */

" " as d3 "Original Reason For Case Openinga and Reassessment of Initial” /*the font size of this needs to change, column with must be changed and the whole column must be shaded in a color and center justified /*

cra8 "Q8", /*column width must be changed and center justified */

&list8a, /*this produces a list of questions from a dictionary table – all question columns need to be formatted  the same*/

cra9 "Q9",  /*column width must be changed and center justified */

cra10 "Q10",  /*column width must be changed and center justified */

&list10a, /*this produces a list of questions from dictionary table – all question columns need to be formatted the same*/

T10a1 "Other Explanation for 10a",  /*column width must be changed and center justified */

ods tagsets.excelxp close;

ods listing;


Accepted Solutions
Solution
‎01-30-2012 07:17 PM
SAS Super FREQ
Posts: 8,740

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi:

  I'm not sure I understand why a divider column, that's empty or blank or missing, would have such long header text. PROC REPORT generally does complain if all of a column that is GROUP or ORDER or ACROSS contains all MISSING values.

  Generally, you can make a "divider" column for PROC REPORT, but in that scenario, you usually don't display header text for the divider column and usually you put at least one character into the column so it's not missing. Then, with a dummy value in the divider column, you can make the foreground and background of the column the same color by changing the style attributes. But, then, it almost seems to me like you would put "Review of CPS History" as a spanning header in the COLUMN statement or in a TITLE statement or in a COMPUTE BEFORE or COMPUTE BEFORE _PAGE_ code block. However, this is all just speculation and guesswork based on an incomplete understanding of what you have, data-wise and what you want, report-wise.

  

  At this point, I'd recommend working with Tech Support. They can look at all your ACTUAL code and your ACTUAL data and help you come to the best solution. In the meantime, here's an example of setting the background and foreground of a column to the same color, using a spanning header and SASHELP.CLASS. In order for you to see the results of this program, you have to run it as posted (without changing the DATA= option or SET statements).

cynthia

data class;

  set sashelp.class;

  blankcol= 'x';

run;

   

ods tagsets.excelxp file='c:\temp\divider_column.xml'

    style=minimal;

proc report data=class nowd

     style(column)={just=c}

     style(header)={foreground=black background=cxcccccc};

  column ("Review of CPS History" name age sex blankcol height weight);

  define blankcol / display ' '

         style(column)={cellwidth=.125in background=cxcccccc foreground=cxcccccc};

run;

ods tagsts.excelxp close;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,740

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi:

  PROC SQL does not afford you the same opportunity to easily adjust report styles as PROC PRINT, PROC REPORT and/or PROC TABULATE.

  If you create your data (with PROC SQL or other procedures or DATA step programs), then you could use STYLE= overrides with PROC REPORT, as shown in the program below (with some fake data from SASHELP.CLASS).

  I only used a few fake columns to illustrate STYLE= overrides, as shown in the screen shot.

cynthia

ods listing close;
** make some fake data;
data matt (keep=d1 craSmiley Happy;
length d1 $50;
  set sashelp.class;
  d1=name;
  cra1 = age;
  cra2 = height;
  cra3 = weight;
  cra4 = age*int(height);
  label cra1 = 'Q1'
        cra2 = 'Q2'
        cra3 = 'Q3'
        cra4 = 'Q4'
        d1 = "Review of CPS History";
run;
                 
** Use PROC REPORT to provide style overrides;
ods tagsets.excelxp file="c:\temp\Final_Raw_Data.xls"
     style=minimal
     options(sheet_interval='none' sheet_name="DATA1");

proc report data=matt nowd
  style(column)={just=c};
  column d1 cra1 cra2 cra3 cra4;
  define d1 / order
         style(header)={font_size=12pt font_face='Times New Roman'}
         style(column)={font_size=10pt font_face='Arial'
                        cellwidth=2in background=cxcccccc};
  define cra1 / display
         style(column)={cellwidth=.5in};
  define cra2 / display
         style(column)={cellwidth=.75in};
  define cra3 / display
         style(column)={cellwidth=1.25in};
  define cra4 / display
         style(column)={cellwidth=.75in};
run;
     
ods _all_ close;


use_proc_report_style_override.jpg
Frequent Contributor
Posts: 142

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi Cynthia--

This is incredibly helpful, thank you!

I have questions:  how do I apply styles to the question lists from the dictionary tables I create? Like &List5a and &list10a. The amount of columns contained in these lists will vary from run to run.

Thank you.

Matt--

SAS Super FREQ
Posts: 8,740

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi:

  If you look in my code,

proc report data=matt nowd

     style(column)={just=c};

you will see that I specified a DEFAULT justification for ALL columns on the PROC REPORT statement. This means the only overrides I need in my DEFINE statements would be anything that I want to be different from what is set on the PROC REPORT statement. You could easily change the font or the colors like this -- as a default for all columns:

proc report data=matt nowd

     style(column)={just=c font_size=9pt font_face='Verdana'};

  If you needed anything more customized than this, you would have to use some SAS Macro coding techniques to customize individual DEFINE statements. You can't use 1 DEFINE statement for multiple items, so you'd need to generate the individual DEFINE statements via a macro program if &list5a items needed to have different style attributes from &list10a items.

cynthia

Frequent Contributor
Posts: 142

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi Cynthia--

Thank you again for your help.

I have been trying do to reproduce the example you gave me and I can't seem to get it to work. I keep getting the warning message :

WARNING: A GROUP, ORDER, or ACROSS variable is missing on every observation.

When I look at the dataset, there is mo data in the observations and I can't open the excel file on the network.

Here is the log:

2152  ** make some fake data;

2153  data work.matt (keep=d1 craSmiley Happy;

2154  length d1 $50;

2155    set Prev.sheet1;

2156    d1=name;

2157    cra1 = age;

2158    cra2 = height;

2159    cra3 = weight;

2160    cra4 = age*int(height);

2161    label cra1 = 'Q1'

2162          cra2 = 'Q2'

2163          cra3 = 'Q3'

2164          cra4 = 'Q4'

2165          d1 = "Review of CPS History";

2166  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line)Smiley SadColumn).

      2160:18

NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).

      2160:13

NOTE: Variable name is uninitialized.

NOTE: Variable age is uninitialized.

NOTE: Variable height is uninitialized.

NOTE: Variable weight is uninitialized.

NOTE: Missing values were generated as a result of performing an operation on missing values.

      Each place is given by: (Number of times) at (Line)Smiley SadColumn).

      22 at 2160:13   22 at 2160:14

NOTE: There were 22 observations read from the data set PREV.SHEET1.

NOTE: The data set WORK.MATT has 22 observations and 156 variables.

NOTE: DATA statement used (Total process time):

      real time           0.25 seconds

      cpu time            0.04 seconds

2167

2168  ** Use PROC REPORT to provide style overrides;

2169  ods tagsets.excelxp file="X:\ORE\Scorecard Database\Final Raw Data.xls"

2170       style=minimal

2171       options(sheet_interval='none' sheet_name="DATA1");

NOTE: Writing TAGSETS.EXCELXP Body file: X:\ORE\Scorecard Database\Final Raw Data.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add options(doc='help') to the ods statement for more

information.

2172

2173  proc report data=work.matt nowd

2174    style(column)={just=c};

2175    column d1 cra1 cra2 cra3 cra4;

2176    define d1 / order

2177           style(header)={font_size=12pt font_face='Times New Roman'}

2178           style(column)={font_size=10pt font_face='Arial'

2179                          cellwidth=2in background=cxcccccc};

2180    define cra1 / display

2181           style(column)={cellwidth=.5in};

2182    define cra2 / display

2183           style(column)={cellwidth=.75in};

2184    define cra3 / display

2185           style(column)={cellwidth=1.25in};

2186    define cra4 / display

2187           style(column)={cellwidth=.75in};

2188  run;

WARNING: A GROUP, ORDER, or ACROSS variable is missing on every observation.

NOTE: There were 22 observations read from the data set WORK.MATT.

NOTE: PROCEDURE REPORT used (Total process time):

      real time           0.07 seconds

      cpu time            0.03 seconds

2189  ods tagsets.excelxp close;

2190  ods listing;

Thank you so much.


SAS Super FREQ
Posts: 8,740

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi:

  In my example, I made some "fake" data -- WORK.MATT from SASHELP.CLASS:

data matt(keep=d1 craSmiley Happy;

length d1 $50;

set sashelp.class;

. . . more . . .

I am not sure why you changed the code.

2153 data work.matt (keep=d1 craSmiley Happy;

2154 length d1 $50;

2155 set Prev.sheet1;

Did you try my code -unchanged- using the SET statement for SASHELP.CLASS??? SASHELP.CLASS should be available to you on your system

If you already have data (such as PREV.SHEET1) which has the variables d1 and cra1, cra2, cra3 and cra4, then you would NOT need to use my DATA step program to make WORK.MATT. You are getting these notes:

NOTE: Variable name is uninitialized.

NOTE: Variable age is uninitialized.

NOTE: Variable height is uninitialized.

NOTE: Variable weight is uninitialized.

Because in my program to make "fake" data, I just used NAME from SASHELP.CLASS to make D1 and used the other numeric variables in SASHELP.CLASS to make the "CRA" variables. I doubt that these variables (NAME, AGE, HEIGHT, WEIGHT) are in your PREV.SHEET1 data. So, by changing the SET statement, you essentially created WORK.MATT with missing values for D1 and CRA1, CRA2, CRA3, CRA4, since the variable used in the assignment statements didn't exist.

As you can see from my screenshot, when I use SAS 9.3 and Excel 2010 and the EXACT code that I posted, I am able to get a file that opens in Excel and has the text "Review of CPS History" in a different font and has the column with D1 (originally NAME variable) highlighted with a background of gray.

Until you get my sample program working, using SASHELP.CLASS, I would not recommend trying it with PREV.SHEET1. However, if you want to use your own data, then I would suggest that you DELETE my DATA step program and change the PROC REPORT step to:

proc report data=prev.sheet1(obs=20) nowd

     style(column)={just=c};

in order to test the code with your PREV.SHEET1 data.

  You might also want to open a track with Tech Support. They can look at all your code and your real data (as opposed to my fake data), to help you figure out the best solution.

cynthia

Frequent Contributor
Posts: 142

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi Cynthia--

Thanks again. I think I know why I got the error message "WARNING: A GROUP, ORDER, or ACROSS variable is missing on every observation" When I used my data, when the column "Review of CPS History" generates there is supposed to be no data in it. Just the colum heading. Its like i devider between questions. So if there is no data in the colum but I need to shade in the colum and format it how would do that if I have a colum with the data in it?

Matt

Solution
‎01-30-2012 07:17 PM
SAS Super FREQ
Posts: 8,740

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Hi:

  I'm not sure I understand why a divider column, that's empty or blank or missing, would have such long header text. PROC REPORT generally does complain if all of a column that is GROUP or ORDER or ACROSS contains all MISSING values.

  Generally, you can make a "divider" column for PROC REPORT, but in that scenario, you usually don't display header text for the divider column and usually you put at least one character into the column so it's not missing. Then, with a dummy value in the divider column, you can make the foreground and background of the column the same color by changing the style attributes. But, then, it almost seems to me like you would put "Review of CPS History" as a spanning header in the COLUMN statement or in a TITLE statement or in a COMPUTE BEFORE or COMPUTE BEFORE _PAGE_ code block. However, this is all just speculation and guesswork based on an incomplete understanding of what you have, data-wise and what you want, report-wise.

  

  At this point, I'd recommend working with Tech Support. They can look at all your ACTUAL code and your ACTUAL data and help you come to the best solution. In the meantime, here's an example of setting the background and foreground of a column to the same color, using a spanning header and SASHELP.CLASS. In order for you to see the results of this program, you have to run it as posted (without changing the DATA= option or SET statements).

cynthia

data class;

  set sashelp.class;

  blankcol= 'x';

run;

   

ods tagsets.excelxp file='c:\temp\divider_column.xml'

    style=minimal;

proc report data=class nowd

     style(column)={just=c}

     style(header)={foreground=black background=cxcccccc};

  column ("Review of CPS History" name age sex blankcol height weight);

  define blankcol / display ' '

         style(column)={cellwidth=.125in background=cxcccccc foreground=cxcccccc};

run;

ods tagsts.excelxp close;

Frequent Contributor
Posts: 142

Re: Formatting Spreadsheets Using Ods Tagsets Excelxp and Proc SQL

Thanks for all yout help Cynthia.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 1329 views
  • 6 likes
  • 2 in conversation