The SAS Output Delivery System and reporting techniques

How to compute column in proc report with Across .

Reply
Contributor
Posts: 32

How to compute column in proc report with Across .

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;

Contributor
Posts: 32

Re: How to compute column in proc report with Across .

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;

Contributor
Posts: 32

Re: How to compute column in proc report with Across .

Hi,

 

 

If any one knows the solution of this please replay.

Super Contributor
Posts: 490

Re: How to compute column in proc report with Across .


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;
Contributor
Posts: 32

Re: How to compute column in proc report with Across .

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. 

Attachment
SAS Super FREQ
Posts: 8,645

Re: How to compute column in proc report with Across .

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

Contributor
Posts: 32

Re: How to compute column in proc report with Across .

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.

SAS Super FREQ
Posts: 8,645

Re: How to compute column in proc report with Across .

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.


SAS Super FREQ
Posts: 8,645

Re: How to compute column in proc report with Across .

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

using_ACROSS_to_create_TEST_column.png

Contributor
Posts: 32

Re: How to compute column in proc report with Across .

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.

 

 

 

Contributor
Posts: 32

Re: How to compute column in proc report with Across .

Hi Cynthia,
Can we use across when we want to across the computed columns and Data_month is incremented field.
SAS Super FREQ
Posts: 8,645

Re: How to compute column in proc report with Across .

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
Post a Question
Discussion Stats
  • 11 replies
  • 801 views
  • 0 likes
  • 3 in conversation