How to compute column in proc report with Across .

[Shantaram]

Hi,

I am trying following code but its not working. 

proc report data=BIUO1.WPK4QR, 
column
Final_Zone
Type data_month,(
leads
Appt
test)
;
define Final_Zone / group 'Zone';
define Type / group 'Type';
define data_month / across;
define leads/ noprint ;
define Appt / noprint;
define test / computed format=percent8.2 'Test';
compute test;
test=(Appt.sum /  leads.sum);
endcomp;
run;

[Shantaram]

Refer the following data.

input final_zone $8. type $ Data_month$ leads appt;
cards;
Central Leads 201601 843 48
Central Walk-in 201601 388 381
EAST Leads 201601 3685 252
EAST Walk-in 201601 573 567
North1 Leads 201601 578 98
North1 Walk-in 201601 180 170
North2 Leads 201601 2452 202
North2 Walk-in 201601 509 493
South Leads 201601 992 195
South Walk-in 201601 190 183
West Leads 201601 1559 285
West Walk-in 201601 195 191
Central Leads 201512 9127 415
Central Walk-in 201512 430 409
EAST Leads 201512 33782 826
EAST Walk-in 201512 999 954
North1 Leads 201512 7815 831
;
run;

[Shantaram]

Hi,

If any one knows the solution of this please replay.

[mohamed_zaki]

proc report data=have;
column Final_Zone Type data_month (leads appt test);
define Final_Zone / group 'Zone';
define Type / group 'Type';
define data_month / across;
define leads/ Analysis Sum  noprint;
define Appt / Analysis Sum noprint;
define test / computed format=percent8.2 'Test';
compute test;
	test = (Appt.SUM /  leads.SUM);
endcomp;
run;

[Shantaram]

Hi Mohamed,

proc report data=have;
column Final_Zone Type data_month, (leads appt test);
define Final_Zone / group 'Zone';
define Type / group 'Type';
define data_month / across;
define leads/ Analysis Sum  noprint;
define Appt / Analysis Sum noprint;
define test / computed format=percent8.2 'Test';
compute test;
	test = (Appt.SUM /  leads.SUM);
endcomp;
run;

Your approach is wright but i want test column under the month.

Please pind the screen shot for reference.

When we use 

column Final_Zone Type data_month, (leads appt test);

this statement we need to add comma then it will show under the month. 

[Cynthia_sas]

Hi:
When you try to compute an item under an ACROSS variable on PROC REPORT, you cannot use the "simple" names such as APPT.SUM and LEADS.SUM or even TEST. That's because with ACROSS variables, PROC REPORT (in a pre-processing phase) assigns absolute column numbers to the items nested within the ACROSS variable. Please look at page 9 on this paper http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

The sample program on page 9 (the COMPUTE block for DIFF) has an example of absolute column numbers in a COMPUTE block.

So in your example, if Final_zone is _c1_ and type is _c2_, then the first variable under data_month for leads would be _c3_ and then apt would be _c4_ and then test would be _c5_. This essentially means you would have a "set" of 3 items under every value for date_month. So your compute block would be something like this:
compute test;
_c5_ = _c4_/_c3_; /* first month */
_c8_ = _c7_/_c6_; /* month 2 */
_c11_=_c10_/_c9_; /* month 3 */
....repeat for each month with diff absolute numbers ...
endcomp;

There have also been quite a few previous forum postings with this same question and the PROC REPORT documentation has a very good section on how PROC REPORT processes a report that describes how absolute column numbers work.

cynthia

[Shantaram]

Hi Cynthia,

I am trying following code.

proc report data=biuo1.WPK4QR out=per_test;
column
Final_Zone
Type
Appt
Met
logins
Issued
WRP_login
WRP_Iss
Data_month,(
leads
Appt_Lead
Met_Appt
Login_Appt
Issued_Appt
Avg_Prem
);
define Final_Zone / group 'Zone';
define Type / group 'Type';
define Data_month / order=data across ;
/*Months*/
define Appt / sum noprint;
define Met / sum noprint;
define logins / sum noprint;
define Issued / sum noprint;
define WRP_login / sum noprint;
define WRP_Iss / sum noprint;
define leads / sum format=Comma10. 'Leads';
define Appt_Lead / computed 'Appt Per Lead(%)' format=percent8.2 ;
compute Appt_Lead;
Appt_Lead = _C3_ /_C9_;
if Type eq 'Walk-in' then Appt_Lead=.;
if Appt_Lead=0 then Appt_Lead=.;
endcomp;
define Met_Appt / computed 'Met Per Appt(%)' format=percent8.2;
compute Met_Appt;
Met_Appt = _C4_ / _C3_;
if Met_Appt=0 then Met_Appt=.;
endcomp;
define Login_Appt / computed 'Login Per Appt(%)' format=percent8.2 ;
compute Login_Appt;
Login_Appt = _C5_ / _C3_;
if Login_Appt=0 then Login_Appt=.;
endcomp;
define Issued_Appt / computed 'Issued Per Appt(%)' format=percent8.2 ;
compute Issued_Appt;
Issued_Appt = _C6_ / _C3_;
if Issued_Appt=0 then Issued_Appt=.;
endcomp;
define WRP_Iss / sum format=Comma10.1 'WRP-Iss';
define Avg_Prem / computed format= comma10. 'Avg Prem' ;
compute Avg_Prem;
Avg_Prem = ((_C8_/ _C6_)* (10**5));
if Avg_Prem=0 then Avg_Prem=.;
endcomp;
break after Final_Zone / summarize skip ol style=[background = BWH font_weight=bold color=black];
rbreak after / summarize ;
rbreak after / summarize;
compute final_zone;
if _break_ = '_RBREAK_' then
final_zone = 'Agency Total' ;
endcomp;
rbreak after / summarize style=[background = BWH font_weight=bold color=black];
run;

I want to across computed columns.

[Cynthia_sas]

Hi:
I really suggest you look at the PROC REPORT documentation. Consider this example I posted:

compute test;
_c5_ = _c4_/_c3_; /* first month */
_c8_ = _c7_/_c6_; /* month 2 */
_c11_=_c10_/_c9_; /* month 3 */
....repeat for each month with diff absolute numbers ...
endcomp;

It would be incorrect for me to have:
compute test;
test = _c4_/_c3_; /* first month */
endcomp;

...and then no other assignment statements for the other months. You must repeat the assignment statement for every value of your ACROSS variable. How many values do you have for your DATA_MONTH variable??? The syntax you show, with only 1 assignment statement) implies that you only have 1 value for your data_month variable.

This construction
data_month,(leads Appt_Lead Met_Appt Login_Appt Issued_Appt Avg_Prem ) implies that data_month will have a usage of ACROSS, which also implies more than 1 value for data_month.

If you notice in my example, I show you that you need an assignment statement for every unique value of your ACROSS variable.

Cynthia

cynthia

Since you did not post data, no one can work with your code. However, the example I posted does show the correct reference syntax.

[Cynthia_sas]

Hi:

  Sorry, I saw that you did post some data and a simplified version of your program in an earlier post. You have not posted this more complicated data. So I went back to that simpler data and modified your original program to create the TEST column. Essentially, the COMPUTE block is the same as what I originally posted, with an addition IF statement added to make sure that there is never a divide by 0 in the assignment statement. Hopefully, this helps explain how the ACROSS works since it is your own example modified and the results are shown with the LEADS and APPTS columns visible and then using NOPRINT.

cynthia

[Shantaram]

Hi Cynthia,

In my data data_month field is incremental field. every month new month code will be add.

We need to automated all those things.

when new month value is added then it will show automatically computed fields for every month no need to add formula every time.

[Shantaram]

Hi Cynthia,
Can we use across when we want to across the computed columns and Data_month is incremented field.

[Cynthia_sas]

Hi:
Yes, you can do that kind of thing if you use SAS macro coding. That takes the program to another level of complexity. In this paper http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf the example of the macro code starts at the bottom of page 12 and extends to the top of page 14. I would warn that you need to be very sure of the working code to start with before you "macroize" it.

cynthia

[mikepage83]

Hi Cynthia,

Is there a reason this would not work? 

I have been trying this forever and even though I have the absolute columns referenced it only works on the first 2 calculated columns and then does not computer on any column after that.

Shown below is a simple data set:

DATE	TOTAL	DEFECTS	DOW	WIDGET
14-Sep-17	21	58	Thu	Widget 1
14-Sep-17	0	48	Thu	Widget 3
14-Sep-17	1	1	Thu	Widget 4
14-Sep-17	20	148	Thu	Widget 5
15-Sep-17	20	65	Fri	Widget 1
15-Sep-17	0	60	Fri	Widget 3
15-Sep-17	19	184	Fri	Widget 5
16-Sep-17	11	46	Sat	Widget 1
16-Sep-17	0	5	Sat	Widget 2
16-Sep-17	0	84	Sat	Widget 3
16-Sep-17	1	2	Sat	Widget 4
16-Sep-17	29	190	Sat	Widget 5
17-Sep-17	15	47	Sun	Widget 1
17-Sep-17	0	1	Sun	Widget 2
17-Sep-17	0	88	Sun	Widget 3
17-Sep-17	0	1	Sun	Widget 4
17-Sep-17	31	193	Sun	Widget 5

And I am trying to run the following code:

Proc report data=TEST  split='^' 
	style(summary)=Header
	style(column)=[font_size=8pt] nowindows;

column 
		WIDGET 
		DATE,DOW, (TOTAL DEFECTS PERCENT)
;

define WIDGET            / group   ' ^Widgets^ ' style={just=left  cellwidth=130};

define DATE              / across  nozero ' ^Date^ ' style={just=center  cellwidth=80} 
							style(column)={tagattr='TYPE:DateTime format:mm/dd/yy;@'} FORMAT=MMDDYY10.;
define DOW            / across   '' style={just=center  cellwidth=130};

define TOTAL              /  sum  'TOTAL' F=COMMA7. STYLE={just=center cellwidth=40};
define DEFECTS              /  sum  'DEFECTS' F=COMMA7. STYLE={just=center cellwidth=40};
define PERCENT				/ computed 'PERCENT' f=PERCENT8.2 style={just=center cellwidth=30}  ;

compute PERCENT;
_c4_ = _c2_ / _c3_ ;
_c7_ = _c5_ / _c6_ ;
_c10_ = _c8_ / _c9_ ;
_c13_ = _c11_ / _c12_ ;
endcomp;

rbreak after / summarize;
quit;
Run;

What would cause this to only add the computed column on _c4_?  I've played around with adding more computed columns and sometimes it adds on _c7_, but never all of them.

Appreciate any help.  I am running 64 bit 9.4 SAS.

[Cynthia_sas]

Hi:
Why do you have both Date and DOW in the ACROSS, wouldn't just one of them work?

Can you post what you're getting now? Also, you didn't post ALL your code -- it appears that  you are trying to send this to Excel -- with the use of TAGATTR, but you don't show ODS statements to indicate whether you are using ODS EXCEL or ODS TAGSETS.EXCELXP.

cynthia

[mikepage83]

Hi Cynthia,

End customer wants to see date and then the DOW below the date...maybe there is a different way to do it than how I am doing it in regards to the Across portion?

Apologies about the code, it is a rather large program and I was just trying to isolate the portion that I thought would be needed.  I've included the XP Tagsets portion for reference below.  Yes, sending to Excel with XP.  I've tried changing the out the compute block with various conditionals i.e.

if not missing(_c11_) and not missing(_c12_) and _c11_ gt 0 and _c12_ gt 0 then _c13_ = (_c11_ / _c12_);

as I thought perhaps missing or 0 values were causing the problem, but nothing seems to work.  I would rather do it in a compute block, but if not I am going to just summarize the data outside of Proc Report (i.e. create a "Total" category) and do my calculations there instead without the need for the rbreak after / summarize.

From every example I have seen online, I can't figure out why this is failing the way it is (i.e. only adding the computed variable to the first column sometimes....sometimes the first 3 columns).  I can't see the original dataset I sent you right now in this window.  I've been playing with trying to bring in less dates or more dates...so if the below computed columns don't add up to the dataset I sent originally, just know that isn't the reason it is failing. 

options missing=0;

ods noresults;
ods listing close;

ods tagsets.ExcelXP file="&outfile" style=sasweb;

ods tagsets.ExcelXP options(
	embedded_titles='yes'
	embedded_footnotes='yes'
	frozen_headers='6'
	embed_titles_once='no'
	sheet_interval='bygroup'
	sheet_label=' '
	suppress_bylines='yes'
	row_repeat='yes'
	rowcolheadings='no'
	Pages_fitwidth='1'
	Font_Size='8pt'
	sheet_name="Data.Widgets"
	hidden_rows='5'
	autofit_height='yes'
	autofit_width='yes'
/*	absolute_column_width='100,100,90,90,90,200,75,30,30*/
	orientation='landscape'
/*	autofilter='all'*/
);
/*Options nonumber nodate nocenter;*/
options nocenter;

title1;
title2;
title3;

ods listing close;
options missing=' ';

Proc report data=TEST  split='^' 
	style(summary)=Header
	style(column)=[font_size=8pt] nowindows;

column 
		WIDGET 
		DATE,DOW, (TOTAL DEFECTS PERCENT)
;

define WIDGET            / group   ' ^Widgets^ ' style={just=left  cellwidth=130};

define DATE              / across  nozero ' ^Date^ ' style={just=center  cellwidth=80} 
							style(column)={tagattr='TYPE:DateTime format:mm/dd/yy;@'} FORMAT=MMDDYY10.;
define DOW            / across   '' style={just=center  cellwidth=130};

define TOTAL              /  sum  'TOTAL' F=COMMA7. STYLE={just=center cellwidth=40};
define DEFECTS              /  sum  'DEFECTS' F=COMMA7. STYLE={just=center cellwidth=40};
define PERCENT				/ computed 'PERCENT' f=PERCENT8.2 style={just=center cellwidth=30}  ;

compute PERCENT;
_c4_ = _c2_ / _c3_ ;
_c7_ = _c5_ / _c6_ ;
_c10_ = _c8_ / _c9_ ;
_c13_ = _c11_ / _c12_ ;
endcomp;

rbreak after / summarize;
quit;
Run;

ods tagsets.ExcelXP close;
ods results;

/*Set titles back to default*/
title The SAS System;
title2;
title3;