I have created the sql code that works fine and outputs correctly according to the report's requirements.
proc sql;
select from_brand,
sum(fi1) as "Expired Contracts - VN"n,
sum(fi2) as "Expired Contracts - VO"n,
sum(su1) as "Renewed - VN - same brand"n,
sum(su2) as "Renewed - VN-VN - same brand"n,
sum(su3) as "Renewed - VN-VO - same brand"n,
sum(su4) as "Renewed - VO - same brand"n,
sum(su5) as "Renewed - VO-VN - same brand"n,
sum(su6) as "Renewed - VO-VO - same brand"n,
sum(refi) as "Follow-Up Financing"n,
sum(su7) as "Renewed - VN - other brand"n,
sum(su8) as "Renewed - VN-VN - other brand"n,
sum(su9) as "Renewed - VN-VO - other brand"n,
sum(su10) as "Renewed - VO - other brand"n,
sum(su11) as "Renewed - VO-VN - other brand"n,
sum(su12) as "Renewed - VO-VO - other brand"n,
sum(su13) as "Renewed - VN - SE-CU"n,
sum(su14) as "Renewed - VN-VN - SE-CU"n,
sum(su15) as "Renewed - VN-VO - SE-CU"n,
sum(su16) as "Renewed - VO - SE-CU"n,
sum(su17) as "Renewed - VO-VN - SE-CU"n,
sum(su18) as "Renewed - VO-VO - SE-CU"n
from
(select distinct from_brand,
case when same_brand=1 and vn_vn in ('10' '11') and _type_=7 then ren else 0 end as su1,
case when same_brand=1 and vn_vn in ('11') and _type_=7 then ren else 0 end as su2,
case when same_brand=1 and vn_vn in ('10') and _type_=7 then ren else 0 end as su3,
case when same_brand=1 and vn_vn in ('00' '01') and _type_=7 then ren else 0 end as su4,
case when same_brand=1 and vn_vn in ('01') and _type_=7 then ren else 0 end as su5,
case when same_brand=1 and vn_vn in ('00') and _type_=7 then ren else 0 end as su6,
case when same_brand=0 and vn_vn in ('10' '11') and _type_=7 then ren else 0 end as su7,
case when same_brand=0 and vn_vn in ('11') and _type_=7 then ren else 0 end as su8,
case when same_brand=0 and vn_vn in ('10') and _type_=7 then ren else 0 end as su9,
case when same_brand=0 and vn_vn in ('00' '01') and _type_=7 then ren else 0 end as su10,
case when same_brand=0 and vn_vn in ('01') and _type_=7 then ren else 0 end as su11,
case when same_brand=0 and vn_vn in ('00') and _type_=7 then ren else 0 end as su12,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('10' '11') and _type_=63 then ren else 0 end as su13,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('11') and _type_=63 then ren else 0 end as su14,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('10') and _type_=63 then ren else 0 end as su15,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('01' '00') and _type_=63 then ren else 0 end as su16,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('01') and _type_=63 then ren else 0 end as su17,
case when same_brand=0 and (se2cu or cu2se) and vn_vn in ('00') and _type_=63 then ren else 0 end as su18
from mkt.ren_hq
outer union corr
select distinct from_brand,
case when both_vn then fin else 0 end as fi1,
case when both_vn=0 then fin else 0 end as fi2
from mkt.fin_hq where _type_=3
outer union corr
select from_brand, count(*) as refi
from mkt.refis group by from_brand
)
group by from_brand;
quit;
The final table looks partly like this, each line represents a distinct brand.
Now I'd like to do the same with a proc report, but I'm struggling in many ways:
How Do I use the filter variables correctly in the compute block and in the define statement?
And why do I get so many lines back in the result? I group by from_brand and by the fake variables which has the same value for all rows of the input table.
Here's my approach so far.
proc report data=mkt.ally out=three;
column _TYPE_ same_brand ren VN_VN fake from_brand su1;
define fake / group noprint;
define from_brand / group ;
define VN_VN / noprint order;
define same_brand / noprint order;
define _TYPE_ / noprint order;
define ren / noprint ;
define su1 / computed ;
break after from_brand / summarize style={background=lightyellow};
compute su1 ;
if same_brand=1 and _type_=7 then su1+ren.sum;
endcomp;
rbreak after/summarize;
run;
... View more
