BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KarimaTouati
Obsidian | Level 7

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) :

 

tabulate_output.PNG 

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

tabulate_2.PNG

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

5 REPLIES 5
Reeza
Super User

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) :

 

tabulate_output.PNG 

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

tabulate_2.PNG

 

Thank you in advance


 

RichardDeVen
Barite | Level 11

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;

RichardADeVenezia_0-1607785552372.png

 

KarimaTouati
Obsidian | Level 7

Thank you all for your responses.

Tom
Super User Tom
Super User

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
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1109 views
  • 6 likes
  • 5 in conversation