topic Re: Proc sql , table with two dimensions in SAS Programming

Proc sql , table with two dimensions

Mirou — Mon, 10 Jun 2019 16:28:37 GMT

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

Re: Proc sql , table with two dimensions

ballardw — Mon, 10 Jun 2019 16:48:56 GMT

@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.

Re: Proc sql , table with two dimensions

Mirou — Tue, 11 Jun 2019 09:33:34 GMT

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

Re: Proc sql , table with two dimensions

LinusH — Tue, 11 Jun 2019 11:09:22 GMT

Then you need PROC REPORT, using COMPUTE block.

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

Re: Proc sql , table with two dimensions

Mirou — Tue, 11 Jun 2019 11:39:57 GMT

thank you !