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
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;
A few options (likely not all) jump to mind:
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
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;
Thank you all for your responses.
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.
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;
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.