topic Re: Report Recommendation Help!!! in ODS and Base Reporting

Report Recommendation Help!!!

JasonNC — Tue, 19 Jan 2016 20:16:24 GMT

I wan to create an output similar to the table shown below.

Buckets are divised based on specifications for that type.The type information and specification informations comes from data source.

Frecst & total Bal comes from an excel file

Var is difference of frecst and Amt

There are 5 types and I need to produce the table like below for all the types and they need to be in one excel tab.

Can Proc tabulate be used for this type or any other suggestion?

Re: Report Recommendation Help!!!

Ksharp — Wed, 20 Jan 2016 01:10:14 GMT

Yes. Proc tabulate can do that , I guess.

It is more like thing of proc report .

What does your data look like ?

Re: Report Recommendation Help!!!

Reeza — Wed, 20 Jan 2016 01:49:55 GMT

Yes, I would also look into ODS Tagsets or Excel to see if you can use BY to automatically generate your multiple tables.

Re: Report Recommendation Help!!!

Cynthia_sas — Wed, 20 Jan 2016 04:35:21 GMT

But PROC TABULATE won't do the subtraction to make the VAR column. So I agree that this is more of a PROC REPORT approach. The true answer is a combination of Reeza's suggestion to use ODS TAGSETS.EXCELXP or ODS EXCEL and then the procedure to use and the type of code to be written will be dependent on the structure of the data (which we haven't seen). The OP needs to either post some sample data and the code that has been attempted so far or provide more detailed information about the structure of the data. The fact is that there is no procedure that will take information from 2 different data sources. So somehow the info from Excel and the other info need to be brought together into 1 dataset prior to the PROC REPORT/PROC TABULATE step. This is a data manipulation exercise that should be fairly straightforward.

cynthia

Re: Report Recommendation Help!!!

JasonNC — Wed, 20 Jan 2016 15:54:56 GMT

The data looks like above .In some cases the fsct amount will be same for all buckets as the fsct might provide total amount and some times it might be divided based on the Buckets in different type.

The total bal and fcst data will be pulled from excel sheet and the bucket and type from data source.

Re: Report Recommendation Help!!!

JasonNC — Wed, 20 Jan 2016 19:00:06 GMT

The data looks like above .In some cases the fsct amount will be same for all buckets as the fsct might provide total amount and some times it might be divided based on the Buckets in different type.

The total bal and fcst data will be pulled from excel sheet and the bucket and type from data source.

Re: Report Recommendation Help!!!

JasonNC — Thu, 21 Jan 2016 05:21:36 GMT

Any help would be appreciated regarding the approach

Re: Report Recommendation Help!!!

Reeza — Thu, 21 Jan 2016 15:23:16 GMT

Post your data as text, so we have sample data to work with.

Re: Report Recommendation Help!!!

JasonNC — Fri, 22 Jan 2016 19:02:42 GMT

Type BucketFrecst_AmtCountDollarsType 1Total Bal1000050010000Type 1Bucket 101003000Type 1Bucket 20100200Type 1Bucket 30100200Type 1Bucket 40100200Type 1Bucket 5000Type 1Bucket 6000Type 1Bucket 7000Type 2Total Bal000Type 2Bucket 1100200500Type 2Bucket 2200100200Type 2Bucket 3300100200Type 2Bucket 450100200Type 2Bucket 52000Type 2Bucket 615000Type 2Bucket 710000Type 3Total Bal1000000Type 3Bucket 10100200Type 3Bucket 20100200Type 3Bucket 3000Type 3Bucket 4000Type 3Bucket 5000Type 3Bucket 6000Type 3Bucket 70100500Type 4Total Bal000Type 4Bucket 130000Type 4Bucket 2200100200Type 4Bucket 3300100200Type 4Bucket 450100200Type 4Bucket 52000Type 4Bucket 615000Type 4Bucket 710050100

Re: Report Recommendation Help!!!

Cynthia_sas — Sat, 23 Jan 2016 17:17:57 GMT

Hi:

I am not entirely clear on what the line is at the top of the report for each type and how it is different from the total line under each type. But, using PROC REPORT, both of these were possible:

1) Both top and bottom totals the same and the % column adds up to 100 both places;

2) Top total is grand total of all types and bottom total is sub total of this type -- the top percent is 100, but the bottom pct is the pct of the grand total

The first report uses by group processing to ensure that every by group is treated as a whole group, so every by group adds up to 100 -- in this case the option nobyline was used to prevent the automatic by line (since the TYPE value was inserted in a COMPUTE BEFORE). The second report does NOT use BY group processing, which allowed the PCTSUM statistic to be generated as a percent of the whole dataset for each row and each type, but the summary line at the top of each table shows the grand total.

Since the data you posted came across without line feeds, my program makes some fake data.

cynthia

** the program....makes 100 obs of fake data;

data fakedata;
length Bucket $15;
infile datalines dlm=',' dsd;
input obsno Type $ Bucket $ Totbal Fcst;
return;
datalines;
"1","Type 1","Bucket1",100000,150000
"2","Type 1","Bucket1",125000,170000
"3","Type 1","Bucket1",400000,450000
"4","Type 1","Bucket1",400000,450000
"5","Type 1","Bucket2",100000,150000
"6","Type 1","Bucket2",125000,170000
"7","Type 1","Bucket3",100000,150000
"8","Type 1","Bucket3",125000,170000
"9","Type 1","Bucket4",100000,150000
"10","Type 1","Bucket4",125000,170000
"11","Type 1","Bucket4",75000,110000
"12","Type 1","Bucket4",75000,110000
"13","Type 1","Bucket5",100000,150000
"14","Type 1","Bucket5",125000,170000
"15","Type 1","Bucket5",425000,470000
"16","Type 1","Bucket5",50000,90000
"17","Type 1","Bucket5",75000,110000
"18","Type 1","Bucket5",425000,470000
"19","Type 1","Bucket5",50000,90000
"20","Type 1","Bucket5",75000,110000
"21","Type 1","Bucket6",100000,150000
"22","Type 1","Bucket6",125000,170000
"23","Type 1","Bucket6",400000,450000
"24","Type 1","Bucket6",50000,90000
"25","Type 1","Bucket6",400000,450000
"26","Type 1","Bucket6",50000,90000
"27","Type 1","Bucket7",100000,150000
"28","Type 1","Bucket7",125000,170000
"29","Type 1","Bucket7",400000,450000
"30","Type 1","Bucket7",425000,470000
"31","Type 1","Bucket7",400000,450000
"32","Type 1","Bucket7",425000,470000
"33","Type 1","Bucket8",100000,150000
"34","Type 1","Bucket8",125000,170000
"35","Type 2","Bucket1",50000,90000
"36","Type 2","Bucket1",75000,110000
"37","Type 2","Bucket1",425000,470000
"38","Type 2","Bucket1",50000,90000
"39","Type 2","Bucket1",425000,470000
"40","Type 2","Bucket1",50000,90000
"41","Type 2","Bucket2",50000,90000
"42","Type 2","Bucket2",75000,110000
"43","Type 2","Bucket3",50000,90000
"44","Type 2","Bucket3",75000,110000
"45","Type 2","Bucket4",50000,90000
"46","Type 2","Bucket4",75000,110000
"47","Type 2","Bucket4",425000,470000
"48","Type 2","Bucket4",50000,90000
"49","Type 2","Bucket4",425000,470000
"50","Type 2","Bucket4",50000,90000
"51","Type 2","Bucket5",50000,90000
"52","Type 2","Bucket5",75000,110000
"53","Type 2","Bucket5",400000,450000
"54","Type 2","Bucket5",400000,450000
"55","Type 2","Bucket6",50000,90000
"56","Type 2","Bucket6",75000,110000
"57","Type 2","Bucket6",425000,470000
"58","Type 2","Bucket6",75000,110000
"59","Type 2","Bucket6",425000,470000
"60","Type 2","Bucket6",75000,110000
"61","Type 2","Bucket7",50000,90000
"62","Type 2","Bucket7",75000,110000
"63","Type 2","Bucket7",50000,90000
"64","Type 2","Bucket7",75000,110000
"65","Type 2","Bucket7",50000,90000
"66","Type 2","Bucket7",75000,110000
"67","Type 2","Bucket8",50000,90000
"68","Type 2","Bucket8",75000,110000
"69","Type 2","Bucket8",400000,450000
"70","Type 2","Bucket8",425000,470000
"71","Type 2","Bucket8",50000,90000
"72","Type 2","Bucket8",400000,450000
"73","Type 2","Bucket8",425000,470000
"74","Type 2","Bucket8",50000,90000
"75","Type 1","Bucket1",300000,350000
"76","Type 2","Bucket1",325000,370000
"77","Type 1","Bucket1",75000,110000
"78","Type 2","Bucket1",75000,110000
"79","Type 1","Bucket2",300000,350000
"80","Type 2","Bucket2",325000,370000
"81","Type 1","Bucket2",400000,450000
"82","Type 2","Bucket2",425000,470000
"83","Type 1","Bucket2",50000,90000
"84","Type 2","Bucket2",75000,110000
"85","Type 1","Bucket2",400000,450000
"86","Type 2","Bucket2",425000,470000
"87","Type 1","Bucket2",50000,90000
"88","Type 2","Bucket2",75000,110000
"89","Type 1","Bucket3",300000,350000
"90","Type 2","Bucket3",325000,370000
"91","Type 1","Bucket3",400000,450000
"92","Type 2","Bucket3",425000,470000
"93","Type 1","Bucket3",50000,90000
"94","Type 2","Bucket3",75000,110000
"95","Type 1","Bucket3",400000,450000
"96","Type 2","Bucket4",425000,470000
"97","Type 1","Bucket4",50000,90000
"98","Type 2","Bucket4",75000,110000
"99","Type 1","Bucket4",300000,350000
"100","Type 2","Bucket4",325000,370000
run;

proc sort data=fakedata;
by type bucket;
run;

** run once with options nobyline active;
** run once with options byline active;
** options nobyline;
** options byline;
**if you use options byline, then take out the compute before _page_ block;

title; footnote;
ods tagsets.excelxp file='c:\temp\alltype.xml'
    style=htmlblue
    options(sheet_interval='none' sheet_name='All Types');

proc report data=fakedata
style(header)={background=darkblue foreground=white fontweight=bold vjust=b}
style(summary)={background=lightblue foreground=black fontweight=bold};
column type bucket n totbal totbal=pctbal fcst diff;
by type;
define type / group page noprint;
define bucket /group style(column)={fontweight=bold};
define n / 'Count';
define totbal / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define pctbal / pctsum f=percent9.2 '%/of/Balance';
define fcst / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define diff / 'Difference' computed f=comma14. style(column)={tagattr='#,###,###,##0'};
compute diff;
    diff=fcst.sum - totbal.sum;
endcomp;
compute before type;
     ** with BY processing the top and bottom lines are the same;
     Bucket='Total Bal';
endcomp;
compute after type;
     Bucket = 'Total';
endcomp;
break before type / summarize;
break after type / summarize;
compute before _page_ / style=Header{background=darkblue foreground=red fontsize=12pt fontweight=bold};
line type $10.;
endcomp;
run;

ods tagsets.excelxp close;
options byline;

title; footnote;
ods tagsets.excelxp file='c:\temp\pct_no_by_type.xml'
    style=htmlblue
    options(sheet_interval='none' sheet_name='All Types No BY');

proc report data=fakedata
style(header)={background=darkblue foreground=white fontweight=bold vjust=b}
style(summary)={background=lightblue foreground=black fontweight=bold};
column type bucket n totbal totbal=pctbal fcst diff;
define type / group page noprint;
define bucket /group style(column)={fontweight=bold};
define n / 'Count';
define totbal / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define pctbal / pctsum f=percent9.2 '%/of/Balance';
define fcst / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define diff / 'Difference' computed f=comma14. style(column)={tagattr='#,###,###,##0'};
compute diff;
    diff=fcst.sum - totbal.sum;
endcomp;
compute after type;
     Bucket = 'Total';
endcomp;
break before type / summarize;
break after type / summarize page;
compute before;
    ** without BY, the break after is the sum and pctsum of each type is divided by the Grand total;
    hold_tb = totbal.sum;
hold_pb = pctbal;
hold_ct = n;
hold_fc = fcst.sum;
hold_df = diff;
endcomp;
compute before _page_ / style=Header{background=darkblue foreground=red fontsize=12pt fontweight=bold};
line type $10.;
endcomp;
compute before type;
     ** put grand totals into the first line for each type;
     Bucket='Total All Types';
totbal.sum = hold_tb;
pctbal = hold_pb;
n=hold_ct;
fcst.sum = hold_fc;
diff = hold_df;
endcomp;
run;

ods tagsets.excelxp close;
options byline;

Re: Report Recommendation Help!!!

JasonNC — Mon, 25 Jan 2016 03:57:11 GMT

Cynthia,

Thank you so much for your reponse.It will be great help

I apologize that i missed some points .

My situation is similar to the By group as shown in first example

The total all is the total based on type

Total in the end is the sum of Buckets from Sum( of Bucket2-Bucket8)

Re: Report Recommendation Help!!!

Cynthia_sas — Sat, 30 Jan 2016 15:36:24 GMT

Hi:

I now understand that the first "total" line is actually in your data and is not being calculated. Also, it appears, from your other information that you want the total number at the top to appear on the report and the first bucket number to appear on the report, but you do NOT want those numbers to be included in the total at the bottom for each TYPE, and that you want the number from that data on the TotalBal line for Amount to be used to calculate the Percent. That means your data is more structured like this (shown with only 2 TYPES):

data fakedata;

length Bucket $15;

infile datalines dlm=',' dsd;

input Type $ ordvar Bucket count amount Fcst;

return;

datalines;

Type 1,1,TotalOut, 100, 1000, .

Type 1,2,Bucket1, 20, 40, 100

Type 1,3,Bucket2, 10, 100, 200

Type 1,4,Bucket3, 20, 150, 170

Type 1,5,Bucket4, 10, 200, 200

Type 1,6,Bucket5, 20, 300, 100

Type 1,7,Bucket6, 20, 19, 50

Type 2,1,TotalOut, 150, 1500, .

Type 2,2,Bucket1, 25, 140, 200

Type 2,3,Bucket2, 15, 120, 300

Type 2,4,Bucket3, 25, 156, 176

Type 2,5,Bucket4, 15, 240, 240

Type 2,6,Bucket5, 25, 360, 230

Type 2,7,Bucket6, 25, 22, 55

;

run;

Since I didn't know how you were going to make sure that TotalOut or TotalBal at the top was going to be on the top, I added a helper variable called "ORDVAR" to my fake data, so that the buckets would stay in order.

You could calculate everything in a DATA step program, but if your data are already summarized, as shown in the data you posted, then once you get the TotalOut or TotalBal line added into the data, it would look like my FAKEDATA above.

So, conceptually, If I understand correctly, you want something like this based on the data&colon;

To do that, you will need to make some helper variables in a DATA step, so that PROC REPORT can do the Total line (in blue) the way you want. There already is one helper variable called ORDVAR in the data. You will basically need a "shadow" variable for each column on the report: COUNT, AMOUNT, FCST and DIFF. In my code below, I name these helper variables USECOUNT, USEAMOUNT, USEFCST and USEDIFF. Remember that ORDVAR=1 is the TotalOut row and ORDVAR=2 is the Bucket1 row in the data. Basically, all the helper variables are assigned missing for the first 2 rows and use the regular variable values on the other rows.:

data finaldata;
set fakedata;
if ordvar in (1,2) then do;
     usecount = .;
     useamount=.;
     useFcst = .;
    usediff = .;
end;
else do;
     usecount=count;
     useamount=amount;
     useFcst = Fcst;
     usediff = useFcst - useamount;
end;
run;

Now with the help of PROC REPORT, and showing all the regular variables and helper variables, you have something like this:

The final PROC REPORT step that used the FINALDATA data set with the helper variables is here:

proc sort data=fakedata;
by type ordvar;
run;

ods html file='c:\temp\finaldata1.html';
proc print data=finaldata;
title '1) what does final data look like with all helper variables';
var type ordvar bucket usecount count useamount amount usefcst fcst usediff;
sum usecount useamount usefcst usediff;
run;
ods html close;

options missing = ' ' nobyline;
title; footnote;

ods html file='c:\temp\alltype.html';
ods tagsets.excelxp file='c:\temp\alltype.xml'
    style=htmlblue
    options(sheet_interval='none' sheet_name='All Types');

proc report data=finaldata
style(header)={background=darkblue foreground=white fontweight=bold vjust=b}
style(summary)={background=lightblue foreground=black fontweight=bold};
title1 '2) Final Report which hides helper columns and shows desired sums for Bucket 2-6';
column type ordvar bucket usecount count useamount amount pctbal
         usefcst fcst usediff diff;
by type;
define type / order page noprint;
define ordvar / order noprint;
define bucket /order order=data style(column)={fontweight=bold};
define usecount / sum noprint;
define count / sum 'Count';
define useamount / sum noprint;
define amount / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define pctbal / computed f=percent9.2 '%/of/Balance';
define usefcst / sum noprint;
define fcst / sum f=comma14. style(column)={tagattr='#,###,###,##0'};
define usediff / sum noprint;
define diff / 'Difference' computed f=comma14.
         style(column)={tagattr='#,###,###,##0'};
compute before ordvar;
     if ordvar = 1 then do;
      ** use HOLDAMT as helper var for division of pctbal;
         holdamt = amount.sum;
     end;
endcomp;
compute ordvar;
     if ordvar = 1 then
     call define(_row_,'style','style=Header{background=lightblue foreground=black fontweight=bold}');
endcomp;
compute diff;
    if ordvar ne 1 then diff = fcst.sum - amount.sum;
endcomp;
compute pctbal;
       if ordvar ne 1 then do;
          if amount.sum gt . then pctbal = (amount.sum / holdamt);
       end;
endcomp;
compute after type;
         Bucket = 'Total';
         count.sum = usecount.sum;
         amount.sum = useamount.sum;
         if useamount.sum gt . then pctbal = useamount.sum / holdamt;
         fcst.sum = usefcst.sum;
         diff = usediff.sum;
endcomp;
break after type / summarize;
compute before _page_ / style=Header{background=darkblue foreground=red fontsize=12pt fontweight=bold};
       line type $10.;
endcomp;
run;

ods _all_ close;
options byline;

I think this comes close to what you described to me in a separate message. Otherwise, I think it is a good approximation.

cynthia