The SAS Output Delivery System and reporting techniques

how to count unique group variables

Reply
N/A
Posts: 0

how to count unique group variables

hello, new to the forum.

hope someone can help me out with this issue.

im running a proc report with various group variables as columns. my issue is how do I have another column that will sum the unique group values and give me a total next to that group?

i tried using compute blocks that had a counter, but i'm not making much progress. any help would be appreciated.

thanks
J Message was edited by: jsjaedon
SAS Super FREQ
Posts: 8,868

Re: how to count unique group variables

Posted in reply to deleted_user
Hi:
Look at some of the examples on page 83 and 84 of this document about PROC REPORT:
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf

In the example on page 82 and 83 of the above document, DEPT is the first report item, ACCOUNT is the second report item. Then DATE is an ACROSS variable and underneath each DATE is the BUDGET variable value for that date (or the sum of the BUDGET variable values for that date in the case of a GROUP variable). The last column on the report is RATIO -- a computed item.

The COMPUTE block for RATIO looks like this:
[pre]
COMPUTE RATIO;
ratio=_c3_ / _c4_;
ENDCOMP;
[/pre]

This kind of referencing is called "absolute column" referencing. In the above report, the items are numbered this way:
[pre]
DEPT ACCT BUDGET for 1st DATE BUDGET for 2nd Date RATIO
_c1_ _c2_ _c3_ _c4_ _c5_
[/pre]

In the compute block, since RATIO is the last column on the report and is not crossed with any other variable, you can refer to it by NAME. However, report item _c3_ (and _c4) is information based on 2 columns of data -- BUDGET for a specific DATE.

If I understand what you want, instead of RATIO -- which is a division, you want to add all the ACROSS variable values on the row. If that's what you mean, then you'll have to get into absolute column references in order to get the right variable values added up. You might have something like this:

[pre]
GRPVar Val for 1st AcrossVar Val for 2nd AcrossVar RowTot
_c1_ _c2_ _c3_ _c4_
[/pre]

and your compute block would be
[pre]
COMPUTE RowTot;
RowTot = sum(_c2_, _c3_);
ENDCOMP;
[/pre]

At any rate, it may require several iterations in order to get it right. The hardest thing is figuring out how many values of the across variable you have, since the simple example above just shows 2 values for the ACROSS variable. For some kinds of cross-tabular reports, you may find it easier to generate your report with PROC TABULATE. But it is hard to say for sure without some idea of your data and your particular report need.

This is one of those instances, where consulting with Tech Support might help you -- you could tell them what your exact report needs are, what your data is like, what kinds of computations you need to perform and they could tell you which procedure would best suit your needs.

cynthia
N/A
Posts: 0

Re: how to count unique group variables

Posted in reply to Cynthia_sas
thanks cynthia for the response. I think I posted a more clear question in this thread.

http://support.sas.com/forums/thread.jspa?threadID=1899&tstart=0

hopefully yuo can help me again, really appreciate it.
Ask a Question
Discussion stats
  • 2 replies
  • 113 views
  • 0 likes
  • 2 in conversation