proc report: how to use variables as filters in compute block and how to summarize

[acordes]

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;

[ballardw]

You show SQL using data sets

mkt.ren_hq

mkt.fin_hq

mkt.refis

And Proc report code using

mkt.ally

with no way for us to know the relationship between the data involved at all.

First thing I would say that you need to learn what a SAS variable LABEL is as all this

sum(fi1) as "Expired Contracts - VN"n, 

makes variables that are extremely hard to work with if need.

Better might be

Sum(fi1) as sumFi1 label='Expired Contracts - VN'

If you are using this to rearrange output from Proc means/summary (from the _type_ variables) I suspect you are making a poor data structure worse for many purposes.

Provide some example of actual data from MKT.Ally so we have a chance of helping.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[acordes]

You’re right, nevertheless I feel the need for explaining myself.

I was in a hurry, so I posted the code like it is without preparing a dataset that can be used for fixing the code.

Furthermore it’s not my questionable idea to name the variables like this or choose the output format I have already attained with the proc sql. It’s my company’s poor data culture and this report goes to headquarters. This time we have to comply with the format and the names as it gets output to excel.

And I thought and still believe that you smart sas gurus catch my idea without having an executable data set attached. And that you can pin me in the right direction.

 Tomorrow I’ll prepare a data set and curate the code with the data sets properly named, etc.

 Meanwhile I highlight what the proc report should accomplish.

 Grouped by only 1 variable and without any across construct, I want to use filters for creating columns. These filters use 3 variables from the data set and I only need them for the sake of filtering. Every of these computed variables sums an existing numeric variable grouped by brand.

[acordes]

Hi @ballardw , sorry for taking so long on explaining better my issue.

I've created an example that works based on sashelp.cars which illustrates my challenge. It does not make sense in the context of sashelp.cars, but let's focus  on my requirement to create new columns based on filter criterias. 

In my real-world scenario I use this construct with more sophisticated filters applied on a table which originated as well from a proc summary.

I want to re-create the output from proc sql but using proc report. 

data cars;
set sashelp.cars;
run;

proc summary data=cars;
where origin='Europe';
class make Cylinders drivetrain;
var Horsepower;
output out=cars_sum(where=(_type_ in (7 5 6))) n=frek;
run;



proc sql;
select make, 
sum(su1) as N_make_cylinders, 
sum(su2) as N_Detail,
sum(su3) as N_make_drivetrain



from 
(select distinct make,
case when _type_=6 then frek else 0 end as su1,
case when _type_=7 then frek else 0 end as su2,
case when _type_=5 then frek else 0 end as su3  
from cars_sum


)
group by make;
quit;