How to modify the table structure in proc tabulate

[KarimaTouati]

Hello,
I am trynig to create this table using proc tabulate as folowing : 

proc tabulate data=outputs.base_sin_102020_v1 (where= (type_sinistre in ('CRP') 
and annee_survenance BETWEEN 2016 AND 2020 and annee_ouverture BETWEEN 2016 AND 2020))
format=10.;
class Annee_Survenance annee_ouverture;
var mnt_rc_bis ;
table Annee_Survenance, annee_ouverture*mnt_rc_bis='REG_DEFENSE' * (sum='') ;
title 'Triangle des règlements de la RC ';
title2 '2020 ';
run;

So, I get this table shape (Table 1) :

 

What can I do to get the shape in Table 2  without manual editing with excel by translating each row to the right ? 

Thank you in advance

[Reeza]

A few options (likely not all) jump to mind:

1. Modify your input data structure so that the code generates the desired output. May need some pre-wrangling/mapping and then stick with PROC TABULATE
2. Manually calculate the values (SQL, data step, proc means/summary) and then display it with PROC REPORT
3. Define a VB macro to shift the data

Those are in order of preference as well. Either way, to try and assist it would be easier if you could provide a fully worked example, in this case use some sample data to replicate your data. Totally fine to make fake data in Excel if you'd like. 

EDIT: please post data as text at minimum, preferably as a data step. Otherwise to work with your data we'd have to type it out from the picture.

---

@KarimaTouati wrote:

Hello,
I am trynig to create this table using proc tabulate as folowing : 

proc tabulate data=outputs.base_sin_102020_v1 (where= (type_sinistre in ('CRP') 
and annee_survenance BETWEEN 2016 AND 2020 and annee_ouverture BETWEEN 2016 AND 2020))
format=10.;
class Annee_Survenance annee_ouverture;
var mnt_rc_bis ;
table Annee_Survenance, annee_ouverture*mnt_rc_bis='REG_DEFENSE' * (sum='') ;
title 'Triangle des règlements de la RC ';
title2 '2020 ';
run;

So, I get this table shape (Table 1) :

 

What can I do to get the shape in Table 2  without manual editing with excel by translating each row to the right ? 

Thank you in advance

---

[RichardDeVen]

The row and column header values in a tabulate can be seen as coordinates, that is an intersection resulting in a cell that contains a single value.

The leftward shift of the cell values you want means there has been a change in what the class variables represent.

The layout of Table 2 requires the columns be based on a interval instead of year.  You will need to compute that interval, and not label the columns as years,because that would be misleading, but label the columns as an interval.

I don't know French, so I made up my own sample data.

Suppose the data is recording the cost of cleanup for various sites cited for violations of environmental regulations.

data have;
  do site_id = 1 to 1000;
    citation_year = rand('integer', 2016, 2020);
    cleanup_cost = rand('integer', 500, 1000);
    do cleanup_year = citation_year to 2020;
      cleanup_cost = rand('integer', 1, cleanup_cost);   * simulate costs as always reducing as clean up proceeds;
      output;
    end;
  end;
run;

ods html file='cost.html' style=plateau;

proc tabulate data=have format=dollar12.;
  title "Table version 1";
  class citation_year cleanup_year;
  var cleanup_cost;

  table
   citation_year,cleanup_year * cleanup_cost * sum=''
  ;
run;

data have2;
  set have;
  years_since_citation = cleanup_year - citation_year;  * compute the interval that will be tabulated in version 2;
run;

proc tabulate data=have2 format=dollar12.;
  Title "Table version 2, with tricks";
  class citation_year years_since_citation;
  var cleanup_cost;

  table
   citation_year = ' '   /* trick: blank label causes empty row to never render */
   , 
   years_since_citation 
   * cleanup_cost * sum=''
   /
   box = {               /* trick: put label in box cell instead */
     label='Citation Year'
     style=[verticalalign=bottom]
   } 
  ;
run;

ods html close;

[KarimaTouati]

Thank you all for your responses.

[Tom]

Your desired table does not make any sense. 

Perhaps you wanted the values to appear based on the difference in the two date variables?  So the headers would be 0,1,...4 instead?

If so then just calculate that new variable and use it instead.

[mkeintz · Accepted solution]

Make a second dataset from the first.  Adjust annee_ouverture  by subtracting  (annee_survenance-2016).  Then rerun the proc tabulate:

data have;
  input annee_survenance annee_ouverture mnt_rc_bis;
datalines;
2016 2016  253484 
2016 2017  112726
2016 2018   70632
2016 2019  209216
2016 2020  113369
2017 2017 1267531
2017 2018  382740
2017 2019  450855
2017 2020  343416
2018 2018 4884853
2018 2019 2425972
2018 2020  554144
2019 2019 12074027
2019 2020  3489196
2020 2020  3106294
run;

proc tabulate data=have format=10.;
  class Annee_Survenance annee_ouverture;
  var mnt_rc_bis ;
  table Annee_Survenance, annee_ouverture*mnt_rc_bis='REG_DEFENSE' * (sum='') ;
  title 'Triangle des règlements de la RC ';
  title2 '2020 ';
run;


data vneed / view=vneed;
  set have;
  annee_ouverture =  annee_ouverture - (annee_survenance-2016);
run;


proc tabulate data=vneed format=10.;
  class Annee_Survenance annee_ouverture;
  var mnt_rc_bis ;
  table Annee_Survenance, annee_ouverture*mnt_rc_bis='REG_DEFENSE' * (sum='') ;
  title 'Triangle des règlements de la RC ';
  title2 '2020 ';
run;