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.
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;
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;
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 cra:);
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;
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--
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
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 cra:);
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):(Column).
2160:18
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
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):(Column).
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.
Hi:
In my example, I made some "fake" data -- WORK.MATT from SASHELP.CLASS:
data matt(keep=d1 cra:);
length d1 $50;
set sashelp.class;
. . . more . . .
I am not sure why you changed the code.
2153 data work.matt (keep=d1 cra:);
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
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
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;
Thanks for all yout help Cynthia.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.