Proc sql , table with two dimensions

[Mirou]

Hello,

i need to create this table using the sql procedure,



i try to work with the sql procedure, because i couldn't calculate the ratio sum(exp_prem)/sum(exposure) using the proc tabulate,
 proc sql ;
 create table ss2 as 
   select code,year
   sum(exposure) as exposition,
   sum(exp_prem)/sum(exposure)as premium,
group by code, year;
quit;

so i get this table as result,


What should i do,
Thank you

[ballardw]

---

@Mirou wrote:

Hello,

i need to create this table using the sql procedure,



i try to work with the sql procedure, because i couldn't calculate the ratio sum(exp_prem)/sum(exposure) using the proc tabulate,
 proc sql ;
 create table ss2 as 
   select code,year
   sum(exposure) as exposition,
   sum(exp_prem)/sum(exposure)as premium,
group by code, year;
quit;

so i get this table as result,


What should i do,
Thank you

---

Why does the report table need to be made by SQL? Either Proc Tabulate or Proc Report make such tables easy from your SS2 table.

Something like:

proc tabulate data=ss2;
   class code year;
   var exposition premium;
   table code='',
         (exposition premium)* year=''* max=''*f=best8.
         /box='Code' misstext=' '
   ;
run;

Proc Report could likely do the ratio needed but nested columns as shown might take more work in Report from the raw data.

[Mirou]

thanks, but how could i insert this formula into the proc tabulate, "PremuimA =sum(premuin)/sum(exposition),"

[LinusH]

Then you need PROC REPORT, using COMPUTE block.

Or if you can have it pre-calculated on detail level first (using SQL), then TABULATE.

[Mirou]

thank you !