The SAS Output Delivery System and reporting techniques

Modify Tabulate Output and ExcelXP Tagsets

Reply
Contributor
Posts: 42

Modify Tabulate Output and ExcelXP Tagsets

Hello,

I HAVE A SAMPLE DATSET WITH 10 OBS SHOWN BELOW:


data sample;
format tran_dt yymmdd10.;
infile datalines;
input co_auth $ kt_auth $ ls_auth $ nr_auth $ co_que $ kt_que $ ls_que $ nr_que $ tran_amt tran_dt yymmdd10.;
datalines;
0 0 0 0 0 0 0 0 100 2011-02-01
2 0 0 0 0 0 1 0 111 2011-02-01
4 0 0 0 1 0 0 0 222 2011-02-03
2 0 0 0 0 0 0 0 200 2011-02-27
2 0 0 0 0 0 0 0 200 2011-02-28
1 0 1 0 1 1 0 0 888 2011-02-24
0 1 0 0 1 0 0 0 223 2011-02-25
0 0 0 0 0 0 0 0 100 2011-02-26
0 0 0 0 1 1 1 1 100 2011-02-22
0 0 0 1 0 0 0 1 001 2011-02-14
0 0 0 0 0 0 0 0 002 2011-02-12
;
run;

I HAVE A DATSTEP WHERE SOME LOGIC IS USED:

data abc;
attrib fal_all_que length=$7.;
set sample;
format proddt date9.;
proddt = '24FEB2011'd;

if (co_auth = '1' or kt_auth = '1' or ls_auth = '1' or nr_auth = '1')
then do;
fal_all = '3.dec';
if fal_all = '3.dec' and (co_que = '1' or kt_que = '1' or ls_que = '1' or nr_que = '1')
then fal_all_que ='que';
else fal_all_que ='not_que';
end;

else if (co_auth in ('2','4') or kt_auth in ('2','4') or ls_auth in ('2','4') or nr_auth in ('2','4'))
then do;
fal_all = '2.ref';
if fal_all = '2.ref' and (co_que = '1' or kt_que = '1' or ls_que = '1' or nr_que = '1')
then fal_all_que = 'que';
else fal_all_que ='not_que';
end;

else if (co_auth = '0' or kt_auth = '0' or ls_auth = '0' or nr_auth = '0')
then do;
fal_all = '1.app';
if fal_all = '1.app' and (co_que = '1' or kt_que = '1' or ls_que = '1' or nr_que = '1')
then fal_all_que = 'que';
else fal_all_que = 'not_que';
end;


if tran_dt < proddt then period = '1.PRE';
if tran_dt >= proddt then period = '2.POST';
run;

THEN I HAVE A COUPLE OF TABULATES WHICH PRODUCES RESULTS SHOWN BELOW:

proc tabulate data=abc(where=(tran_dt ne proddt)) missing ;
class fal_all period ;
var tran_amt;
table fal_all='Action' all='Total',
period=' '*tran_amt=' ' * (n ='Unit'*f=5.
sum ='Dollar'*f=6.
colpctn ='unit%' *f=5.2
colpctsum ='dollar%'*f=7.2
) / BOX=" TEST" rts=10 row=float ;
run;

proc tabulate data=abc(where=(tran_dt ne proddt)) missing ;
class fal_all_que period ;
var tran_amt;
table fal_all_que='Action' all='Total',
period=' '*tran_amt=' ' * (n ='Unit'*f=5.
sum ='Dollar'*f=6.
colpctn ='unit%' *f=5.2
colpctsum ='dollar%'*f=7.2
) / BOX="TEST" rts=10 row=float ;
run;




----------------------------------------------------------------
| TEST | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Action | | | | | | | | |
|--------| | | | | | | | |
|1.app | 3| 202|50.00| 37.69| 1| 100|25.00| 13.83|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|2.ref | 2| 333|33.33| 62.13| 2| 400|50.00| 55.33|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|3.dec | 1| 1|16.67| 0.19| 1| 223|25.00| 30.84|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00|
----------------------------------------------------------------


----------------------------------------------------------------
|TEST | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Action | | | | | | | | |
|--------| | | | | | | | |
|not_que | 2| 102|33.33| 19.03| 3| 500|75.00| 69.16|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|que | 4| 434|66.67| 80.97| 1| 223|25.00| 30.84|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00|
----------------------------------------------------------------

|

I have a couple of question that I need help with.

First:
=======

I am outputting these tables in excel using excelxp tagsets. In the first output, I am hoping if there is a way where I can add a column in the end and calculate the unit%

difference between 1.PRE and 2.POS. I tried column calculations using =RC(1) etc logic but not with much luck.

So the output in excel (or in listing) would look something like:

---------------------------------------------------------------------------
| TEST | 1.PRE | 2.POS | |
| |--------------------------+--------------------------|-----------
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|unit% diff |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|Action | | | | | | | | | |
|--------| | | | | | | | |
|1.app | 3| 202|50.00| 37.69| 1| 100|25.00| 13.83| -50.00 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|2.ref | 2| 333|33.33| 62.13| 2| 400|50.00| 55.33| 16.67 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|3.dec | 1| 1|16.67| 0.19| 1| 223|25.00| 30.84| 8.33 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00| |
---------------------------------------------------------------------------

SECOND:
========
Second question I have is, in the 2nd output above, I would like to display ONLY the 'que' line. I can get rid of 'Total' by removing all in the tabulate code but I want to remove

'not_que' line as well. I want to show ONLY the 'que' line but I want all the number for the que line to be 'as is'.

Also, is there a way to post the listing output table in a nicer way Smiley Happy.

Hope to hear from you all soon.

Thank you,

P.S.
SAS Super FREQ
Posts: 8,744

Re: Modify Tabulate Output and ExcelXP Tagsets

Hi:
To post the LISTING output in a nicer way (and to present your code with all the indention maintained, refer to this previous forum posting.
http://support.sas.com/forums/thread.jspa?messageID=27609毙

In particular it discusses how to emphasize code and how to surround code snippets with [pre] and [/pre] -- you will need to use these tags before and after your code or LISTING output to make them look nicer when posted.

(Also, in order to protect > and < symbols, and to otherwise learn how to post special symbols to the forum, it is a good resource.)

To answer your first question, however, TABULATE cannot calculate "new" report columns. That is something that PROC REPORT can do (in a COMPUTE block) and there are many examples in previous forum postings of using PROC REPORT to add extra report columns that need to be calculated from other report items.

Other folks "pre-calculate" or create the new column in a datastep program prior to the PROC TABULATE step -- this may work for some statistics -- usually OK for simple sums or subtraction -- might not work for percents or more complicated formulas.

To take a stab at your second question, since I can't really see what you are talking about in the jumble of lines, I'm not sure whether you want to get rid of the string 'Action' or the whole ROW with the numbers for 'Action'?? I don't see not_que in your table, I only see rows for Action, and 1.app, 2.ref and 3.dec and Total. so I'm not sure what you want to do. If you want to get rid of the "double high" row in the table, you can do that by using the BOX= option and blanking out the row header 'Action', but if you are going to have to switch to REPORT to get your calculated column, then it becomes a moot point.

cynthia
Contributor
Posts: 42

Re: Modify Tabulate Output and ExcelXP Tagsets

[pre]
Hi Cynthia,

Thank you for the reply and the link. Here is what I wanted to do:



FOR MY FIRST QUESTION:
======================
My first proc tabulate produces a table shown below:

[pre]

proc tabulate data=abc(where=(tran_dt ne proddt)) missing ;
class fal_all period ;
var tran_amt;
table fal_all='Action' all='Total',
period=' '*tran_amt=' ' * (n ='Unit'*f=5.
sum ='Dollar'*f=6.
colpctn ='unit%' *f=5.2
colpctsum ='dollar%'*f=7.2
) / BOX=" TEST" rts=10 row=float ;
run;


----------------------------------------------------------------
| TEST | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Action | | | | | | | | |
|--------| | | | | | | | |
|1.app | 3| 202|50.00| 37.69| 1| 100|25.00| 13.83|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|2.ref | 2| 333|33.33| 62.13| 2| 400|50.00| 55.33|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|3.dec | 1| 1|16.67| 0.19| 1| 223|25.00| 30.84|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00|
----------------------------------------------------------------

I output this table in excel using ods excelxp tagsets and I was hoping if there a way to add a column in excel when I output this table in excel. So in excel this table will be outputted with an extra column which will calculate the difference between unit% between 2.POS and 1.PRE.
I wanted the excel to look something like:

---------------------------------------------------------------------------
| TEST | 1.PRE | 2.POS | |
| |--------------------------+--------------------------|-----------
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|unit% diff |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|Action | | | | | | | | | |
|--------| | | | | | | | |
|1.app | 3| 202|50.00| 37.69| 1| 100|25.00| 13.83| -50.00 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|2.ref | 2| 333|33.33| 62.13| 2| 400|50.00| 55.33| 16.67 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|3.dec | 1| 1|16.67| 0.19| 1| 223|25.00| 30.84| 8.33 |
|--------+-----+------+-----+-------+-----+------+-----+-------|-----------
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00| |
---------------------------------------------------------------------------

As you have mentioned we cannot add a new column in proc tabluate output. But I was hoping, since I am outputting this to excel and using excelxp tagset if there is a way to write a formula in the excelxp options which does this for me.
Otherwise I might have to use report with computed.

MY SECOND QUESTION:
==================
The second proc tabulate produces this output shown below.

proc tabulate data=abc(where=(tran_dt ne proddt)) missing ;
class fal_all_que period ;
var tran_amt;
table fal_all_que='Action' all='Total',
period=' '*tran_amt=' ' * (n ='Unit'*f=5.
sum ='Dollar'*f=6.
colpctn ='unit%' *f=5.2
colpctsum ='dollar%'*f=7.2
) / BOX="TEST" rts=10 row=float ;
run;

----------------------------------------------------------------
|TEST | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Action | | | | | | | | |
|--------| | | | | | | | |
|not_que | 2| 102|33.33| 19.03| 3| 500|75.00| 69.16|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|que | 4| 434|66.67| 80.97| 1| 223|25.00| 30.84|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|Total | 6| 536|100.0| 100.00| 4| 723|100.0| 100.00|
----------------------------------------------------------------

Now I only want to display the 'que' line and not the 'not_que' and 'total'. I can remove the 'total' by removing all='Total' in the proc tablulate code above but I want to remove not_que as well. So I want some thing like if possible.

----------------------------------------------------------------
|TEST | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|que | 4| 434|66.67| 80.97| 1| 223|25.00| 30.84|
|--------+-----+------+-----+-------+-----+------+-----+-------|

[/pre]

Thank you,
P.S.
Valued Guide
Posts: 2,175

Re: Modify Tabulate Output and ExcelXP Tagsets

take advantage of the denominator definition allowing an alternate analysis variable.
Rather than class by Q/non-Q create an alternate analysis variable which is non-missing when you want it counted
You seek
Sum( when it is "que")
--------------------------------------
Sum( over all rows )
This attempts to achieve this based on your intermediate data creating Q_amt only when fal_all_que='que'
data def ;
set abc(where=(tran_dt ne proddt)) ;
if fal_all_que = 'que' the Q_amt = tran_amt ;
run ;
Next, tabulate with this alternate analysis variable[pre]proc tabulate data=def missing ;
class period ;
var tran_amt Q_amt ;
table all='TEST', period=' '*Q_amt=' ' * (n ='Unit' *f=5.
sum ='Dollar' *f=6.
pctn ='unit%' *f=5.2
pctsum ='dollar%'*f=7.2
) / BOX=" " rts=10 row=float ;
run;[/pre]That creates the following listing (anyone like to tell me how to require Sas monospace font here)[pre]
----------------------------------------------------------------
| | 1.PRE | 2.POS |
| |--------------------------+--------------------------|
| |Unit |Dollar|unit%|dollar%|Unit |Dollar|unit%|dollar%|
|--------+-----+------+-----+-------+-----+------+-----+-------|
|TEST | 4| 434|66.67| 80.97| 1| 223|25.00| 30.84|
----------------------------------------------------------------

[/pre]
Ask a Question
Discussion stats
  • 3 replies
  • 363 views
  • 1 like
  • 3 in conversation