The data I am using for my question is from SASHELP for the purpose of formulating my question. The reason I have removed data for two months for IBM is in order to simulate the fact that some of the companies in my monthly data only contain end of the quarter data.
Where I am stuck is that for these companies with only end of qtr data (which I have placed towards the end of the table), I would like to center the display within the three column cell (as shown in the second table shown below the code).
The destination is PDF and I am using SAS 9.4 (release 3). My code is shown below.
Thanks,
Aroop
FILENAME pdfrep '~/stock.pdf';
data stocks;
set sashelp.stocks (where=(year(date)=2005));
Quarter=QTR(date);
if stock="IBM" and Month(date) not IN(3,6,9,12)
then call missing(close);
run;
proc sort; by descending stock date;run;
option missing='-';
ods pdf file=pdfrep;
proc report nowd;
format date monyy7. Quarter roman3.;
column stock close,(Quarter,date);
define stock/group order=data;
define close/analysis ' ' ;
define Quarter/across ' ' nozero;
define date/across ' ' order=data;
run;
ods pdf close;
Hi:
When you're starting out, I do recommend restructuring your data so that it is "flattened" and you don't need a more complex DATA step to create your table. Neither REPORT nor TABULATE will do the kind of column spanning you want to achieve. So if it was up to me, I'd try to convince my users to use the PROC REPORT or TABULATE version of the report with the multiple columns for each quarter, because after all, there ARE multiple months in each quarter.
It is possible to generate something that mimics ACROSS with DATA step, but the coding is much more complex and sort of a headache to plan out. Possible, but not as easy as transposing your data or living with PROC REPORT output.
I'm back at my computer and changed the data a bit to so it would test ALL the possible conditions (If I had 2 values, if I had only 1 value, if I had no values):
My data for the program pretty much was already structured to make the simplest code possible. I used a DATALINES to read the data, but could have done a transpose too (it was just easier to make data:
data fakedata;
infile datalines;
input Category $ fheight mheight fweight mweight;
datalines;
Cat1 . . . .
Cat2 51.30 57.50 50.50 85.00
Cat3 60.90 62.50 91.00 84.00
Cat4 63.55 . 96.25 .
Cat5 64.50 66.75 112.25 122.50
Cat6 . 72.00 . 150.00
;
run;
Then here's my program to produce the output:
ods pdf file="c:\temp\col_span_rwi.pdf" ;
title 'Span Columns in Header and Data Cells';
footnote;
data _null_;
set fakedata end=last;
if _N_ = 1 then do;
dcl odsout obj();
obj.table_start();
obj.head_start();
** Header row 1;
obj.row_start(type:"Header");
obj.format_cell(text: " ");
obj.format_cell(text: "Averages By Gender", column_span:4, style_attr:"color=black fontweight=bold");
obj.row_end();
** Header row 2;
obj.row_start(type:"Header");
obj.format_cell(text: " ");
obj.format_cell(text: "F", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "M", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "F", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "M", style_attr:"color=black fontweight=bold");
obj.row_end();
** Header row 3;
obj.row_start(type: "Header");
obj.format_cell(text: "Category", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Height", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Height", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Weight", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Weight", style_attr:"color=black fontweight=bold");
obj.row_end();
obj.head_end();
end;
** start a row for every obs;
** figure out the number of missing values for the HEIGHT/WEIGHT variables using nmiss;
missht = nmiss(fheight, mheight);
misswt = nmiss(fweight, mweight);
obj.row_start();
obj.format_cell(data: Category );
if missht = 0 then do;
** if nothing is missing, write both values for HEIGHT;
obj.format_cell(data: fheight);
obj.format_cell(data: mheight);
end;
else if missht = 1 then do;
** if one value is missing, figure out which value is there ;
** and write the value with column spanning;
if fheight gt . then obj.format_cell(data: fheight, column_span:2);
else if mheight gt . then obj.format_cell(data: mheight,column_span:2);
end;
else if missht = 2 then do;
** if both values are missing, just write n/a in the cell;
obj.format_cell(text: "n/a",column_span:2);
end;
** Repeat the logic for the WEIGHT values in the next 2 columns;
if misswt = 0 then do;
obj.format_cell(data: fweight);
obj.format_cell(data: mweight);
end;
else if misswt = 1 then do;
if fweight gt . then obj.format_cell(data: fweight, column_span:2);
else if mweight gt . then obj.format_cell(data: mweight,column_span:2);
end;
else if misswt = 2 then do;
obj.format_cell(text: "n/a",column_span:2);
end;
obj.row_end();
if last then do;
obj.table_end();
end;
run;
ods pdf close;
Hope this helps,
Cynthia
Hello @ghosh,
Your question requires more details before experts can help. Can you revise your question to include more information?
Review this checklist:
To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.
SAS experts are eager to help -- help them by providing as much detail as you can.
This prewritten response was triggered for you by fellow SAS Support Communities member @Reeza
.Hi Reeza,
If the month is at the end of the quarter, then that's the month for which I have the data for that company. The other months are coded as missing, hence I put in the following line to simulate my data:
If stock ="IBM" AND month(date) not in(3,6,9,12)
Thanks
Aroop
Hi:
PROC REPORT will not span columns such as you display for the row for IBM. You can get rid of the missing "dots" in the first 2 months of the quarter on the row for IBM, but if every quarter has 3 months, then PROC REPORT will want the row for IBM to have the same number of columns as the row for Intel and the row for Microsoft.
You can do the kind of column spanning in the data cells you want using the Report Writing Interface. For example, this report using some fake data was created using the Report Writing Interface. Note the row for Age 16 compared to the other rows.
Cynthia
Cynthia,
This is exactly what I have been trying to do, could you show me please how you managed to center the numbers by using column spanning? This will be a perfect solution. And will your solution work in a PDF destination?
The way I have been try to accomplish it is by moving the end of the quarter month value to the previous month and hide the vertical border around it, but have not been able to suppress the vertical border for the cells with the values.
Thanks,
Aroop
Hi:
Take a look at this paper: http://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf -- In this paper, I used the Report Writing Interface techniques shown in Example #5. I used the column spanning attribute to get the spanning to happen based on whether I had 2 items to fill columns 2 and 3 and 2 columns to fill columns 4 and 5.
The challenge is that I did NOT use PROC REPORT for this report. When I am back on a computer with SAS, I will post an example.
Cynthia
Thanks Cynthia for the link to your paper. It's quite thorough, I have quickly read through it and a few others including a couple by Peter Lund. I intend to delve deeper if in fact it is the only way to achieve my goal. RWI seems like a serious alternative to Proc Report/Tabulate especially one needs the rowspan and colspan features.
However, I have been unable to find any reference to simulate the "across" feature found in Proc Report. Unless we are meant to transpose the dataset to achieve this functionality.
Aroop
Hi:
When you're starting out, I do recommend restructuring your data so that it is "flattened" and you don't need a more complex DATA step to create your table. Neither REPORT nor TABULATE will do the kind of column spanning you want to achieve. So if it was up to me, I'd try to convince my users to use the PROC REPORT or TABULATE version of the report with the multiple columns for each quarter, because after all, there ARE multiple months in each quarter.
It is possible to generate something that mimics ACROSS with DATA step, but the coding is much more complex and sort of a headache to plan out. Possible, but not as easy as transposing your data or living with PROC REPORT output.
I'm back at my computer and changed the data a bit to so it would test ALL the possible conditions (If I had 2 values, if I had only 1 value, if I had no values):
My data for the program pretty much was already structured to make the simplest code possible. I used a DATALINES to read the data, but could have done a transpose too (it was just easier to make data:
data fakedata;
infile datalines;
input Category $ fheight mheight fweight mweight;
datalines;
Cat1 . . . .
Cat2 51.30 57.50 50.50 85.00
Cat3 60.90 62.50 91.00 84.00
Cat4 63.55 . 96.25 .
Cat5 64.50 66.75 112.25 122.50
Cat6 . 72.00 . 150.00
;
run;
Then here's my program to produce the output:
ods pdf file="c:\temp\col_span_rwi.pdf" ;
title 'Span Columns in Header and Data Cells';
footnote;
data _null_;
set fakedata end=last;
if _N_ = 1 then do;
dcl odsout obj();
obj.table_start();
obj.head_start();
** Header row 1;
obj.row_start(type:"Header");
obj.format_cell(text: " ");
obj.format_cell(text: "Averages By Gender", column_span:4, style_attr:"color=black fontweight=bold");
obj.row_end();
** Header row 2;
obj.row_start(type:"Header");
obj.format_cell(text: " ");
obj.format_cell(text: "F", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "M", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "F", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "M", style_attr:"color=black fontweight=bold");
obj.row_end();
** Header row 3;
obj.row_start(type: "Header");
obj.format_cell(text: "Category", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Height", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Height", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Weight", style_attr:"color=black fontweight=bold");
obj.format_cell(text: "Weight", style_attr:"color=black fontweight=bold");
obj.row_end();
obj.head_end();
end;
** start a row for every obs;
** figure out the number of missing values for the HEIGHT/WEIGHT variables using nmiss;
missht = nmiss(fheight, mheight);
misswt = nmiss(fweight, mweight);
obj.row_start();
obj.format_cell(data: Category );
if missht = 0 then do;
** if nothing is missing, write both values for HEIGHT;
obj.format_cell(data: fheight);
obj.format_cell(data: mheight);
end;
else if missht = 1 then do;
** if one value is missing, figure out which value is there ;
** and write the value with column spanning;
if fheight gt . then obj.format_cell(data: fheight, column_span:2);
else if mheight gt . then obj.format_cell(data: mheight,column_span:2);
end;
else if missht = 2 then do;
** if both values are missing, just write n/a in the cell;
obj.format_cell(text: "n/a",column_span:2);
end;
** Repeat the logic for the WEIGHT values in the next 2 columns;
if misswt = 0 then do;
obj.format_cell(data: fweight);
obj.format_cell(data: mweight);
end;
else if misswt = 1 then do;
if fweight gt . then obj.format_cell(data: fweight, column_span:2);
else if mweight gt . then obj.format_cell(data: mweight,column_span:2);
end;
else if misswt = 2 then do;
obj.format_cell(text: "n/a",column_span:2);
end;
obj.row_end();
if last then do;
obj.table_end();
end;
run;
ods pdf close;
Hope this helps,
Cynthia
Hi Cynthia,
Thank you so much for your detailed response. Such a coincidence I was just watching your 2016 SGF presentation on this topic when I received your response.
However the fact is my data is quite complex and adapting your suggestion will take a great deal of time, for my next project I will definitely use the RWI.
Meanwhile if I already have my table as below, I am trying to remove the vertical borders around the IBM numbers. I am using Call define to achieve it but not able to get the style to work properly. Could you suggest please how I may I suppress the vertical borders around the IBM numbers?
Thanks,
Aroop
Hi:
If I needed RTF or PDF output, I'd switch to a style like JOURNAL that doesn't have any interior table lines. For HTML and a style like HTMLBLUE, which has a white background, I would change all the appropriate left and right border colors to be white.
But honestly, I would NOT do this. I'd train my users that if there was only one value in a quarter, it was the quarterly value. Or, I'd convince them to only have monthly values or quarterly values, but not mix like this.
As you see, changing the borders is harder than you might want because not only do you have to "turn off" the borders for the middle cell (in this case, y1), but you also have to change the colors for the borders that touch from the neighboring cells.
Cynthia.
Thanks Cynthia. I will try both solutions.
Aroop
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.