BookmarkSubscribeRSS Feed
P_S_
Obsidian | Level 7
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 :).

Hope to hear from you all soon.

Thank you,

P.S.
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
P_S_
Obsidian | Level 7
[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.
Peter_C
Rhodochrosite | Level 12
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1227 views
  • 1 like
  • 3 in conversation