Columns and totals (report or computab) ?

[deleted_user]

Hi - I am trying to create a report from the following data set.

data mydata;
length product $ 8;
input product :$8. date :mmddyy10.
vol bud oper volap ;
format date ddmmyy10.;
datalines;
prod01 01/31/2009 4600 4300 2200 4480
prod02 01/31/2009 4700 4330 2300 5500
prod03 01/31/2009 4800 4360 2600 4500
prod01 02/28/2009 4900 5000 2100 4430
prod02 02/28/2009 5100 5480 2400 5510
prod03 02/28/2009 4600 4300 2200 4480
prod01 03/31/2009 4700 4330 2300 5500
prod02 03/31/2009 4800 4360 2600 4500
prod03 03/31/2009 4900 5000 2100 4430
prod01 04/30/2009 0 5000 0 4000
prod02 04/30/2009 0 5000 0 4000
prod03 04/30/2009 0 5000 0 4000
;
run;

I would like to see ....

product item 31/1/2009 28/2/2009 31/3/2009 30/4/2009 TOTAL
prod01 vol 4600 4900 etc
bud 4300 5000 etc
%(calc)

prod02 vol
bud
%

..
...

total

I have been trying desperately with computab and report - but can't get what I want.

The closest I come to is the following code :
However, I would really like for it all to be part of the same table and show the actual dates.

proc sort data=mydata; by product; run;


proc computab data=mydata ;
by product;
columns product date1-date4/_name_ ;
array collist[4] date1-date4;

rows vol / 'Volume';
rows bud / 'Budget' f=5.2;
rows att / '% ';

i = month( date );
collist = 1;
rowcalc:
if vol = . then return;
att = vol/bud*100;
run;



I would also be willing to use proc report if someone can help.

[deleted_user]

ok : got part of it .....

title 'My New Report';



PROC REPORT DATA=mydata NOWINDOWS;
COLUMN product date, (vol bud);
DEFINE product / '' GROUP;

DEFINE date / '' ACROSS ORDER=INTERNAL;

DEFINE vol / 'Volume' ;
DEFINE bud / 'Budget' FORMAT=6. ;

RUN;

title;

now if someone could help getting vol and budget on seperate lines !!
(Then I need to worry about totals and percentages)

[Cynthia_sas]

Hi:
Proc Report will not put vol and bud on separate lines, given the way that your data is currently structured.

One solution would be to "break apart" your observation so that report would give you one row for vol and another row for bud, within some other grouping variable.

A totally different solution would be to move to a custom Table Template, where you can stack multiple values in one cell. However, in that instance, your data would need to be restructured for the table template, because table templates, by themselves, will not automatically "flip" your data, as PROC REPORT does with the ACROSS usage.

There is a 3rd possibility, depending on what your destination of choice is. That would be to make a "big" character string to hold both vol and bud -- formatted the way you want -- and then you would insert a "line feed" character in between the 2 formatted values using ODS ESCAPECHAR.

I didn't look very closely at your data, but if you are -not- needing to compute any columns or customize the break line, you may want to try PROC TABULATE, which sometimes does better on slicing and dicing your data if all you need is a table. If there's nothing out of the ordinary about the kind of table you want, you might want to try the TABULATE road first.

cynthia

Given your data, this is a TABULATE that puts VOL and BUD under each date and then summarizes them in a final Grand Total row. For more help with percents, you might consult the TABULATE documentation or work with Tech Support:
[pre]
ods html file='c:\temp\mytable.html' style=sasweb;
proc tabulate data=mydata f=comma6.;
class product date;
var vol bud;
table (product all)*(vol bud),
date
/row=float;
keylabel sum=' ';
run;
ods _all_ close;


[/pre]

[deleted_user]

Thanks.
Tabulate won't work for me because at the end I wanted to use a Cumalitive YTD total and %.

What inspired me was the following two computab samples.
http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/etsug_computab_sect032.htm

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/etsug_computab_sect031.htm

I was trying to combine the two.

I am willing to retructure my data in any way possible if there is an alternative solution.

The final destination will most probably end up in a stored procedure that will be used in either excel or web report studio.

Thanks.

[Cynthia_sas]

Hi:
Maybe this will give you an idea of computing a cumulative variable and a percent of total versus a percent of a group. We use an example similar to this in our report class.
[pre]
** make some data with only a few products and regions;
proc sort data=sashelp.shoes out=shoes;
by region product;
where region in ('Asia', 'Canada', 'Pacific') and
product in ('Boot', 'Slipper', 'Sandal');
run;

options missing = ' ';
ods html file='cum_grppct.html' style=sasweb;

proc report data=shoes nowd ;
title '2) Percent PROC REPORT';
title2 'Calculated with Temporary Variables';
column region
product sales PctGrp cumsales sales=psal;
define region / group 'Region' f=$25.;
define product / group 'Product';
define sales / sum 'Sales' f=comma14.;
define cumsales / computed 'Cum Sales' f=comma14.;
define pctgrp / computed 'Percent of Region' f=percent9.2;
define psal / pctsum 'Percent of Total' f=percent9.2;

break after region / summarize;
rbreak after / summarize;

compute before region;
grptot=sales.sum;
holdsales = 0;
endcomp;

compute PctGrp;
PctGrp=sales.sum / grptot;
endcomp;

compute cumsales;
holdsales + sales.sum;
if _break_ = ' ' then cumsales = holdsales;
else cumsales = sales.sum;
endcomp;

compute after region /
style=Header;
line ' ';
endcomp;
compute after;
PctGrp = .;
region = 'Overall Report';
endcomp;
run;

ods _all_ close;
[/pre]

This report uses some temporary variables to accumulate cum sales and for calculating the percent of group. This is getting into some advanced PROC REPORT topics. You might want to read the section in the PROC REPORT documentation on how PROC REPORT performs processing to build a report:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473631.htm

In addition, some other documentation which you might find helpful are papers by Art Carpenter on PROC REPORT and the COMPUTE block or Technical Report P-258, about using PROC REPORT in "batch" on non-interactive mode:
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf (even though this is older documentation, it is still good PROC REPORT information.

cynthia