Center cell contents for "across variable" for company where only quarterly data is available

[ghosh]

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;
 

[Community_Guide]

Hello @ghosh,

Your question requires more details before experts can help. Can you revise your question to include more information? 

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
  
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

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

.

[Reeza]

Sorry, hit that by accident! Do you have an indicator in your data set that will tell you if an observation is quarterly or monthly?

[ghosh]

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

[Cynthia_sas]

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

[ghosh]

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  

[Cynthia_sas]

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

[ghosh]

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

[Cynthia_sas · Accepted solution]

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

[ghosh]

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

[Cynthia_sas]

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.

[ghosh]

Thanks Cynthia.  I will try both solutions.

Aroop