Another Proc Report Question

Scottcom4 — Wed, 04 Mar 2009 06:15:51 GMT

Wow. How much trouble am I having with these Proc Reports.

I am hoping that someone can help me once again.

The following code allows me to produce a report with all my coverage codes as variables, however the observations displayed are simply a count of how many times the each coveragecode appears in the dataset I am using. What I wish to do is place my calculated frequency in these colums. Is this possible? If so, can someone help me with the syntax.

Thank you very much.

options nodate pageno=1 linesize=64
pagesize=60;
proc report Data=Merged Out = Frequency headline headskip;
column exposmth coveragecode EarnedCov Claimcount Frequency;
define coveragecode / Across ;
define exposmth / Group;
define EarnedCov / Analysis Sum noprint;
define Claimcount / Analysis Sum noprint ;
define Frequency / Computed format = percent9.2;

compute Frequency;
Frequency = Claimcount.sum / EarnedCov.sum;
endcomp;
run; Message was edited by: Scottcom4

Re: Another Proc Report Question

Cynthia_sas — Thu, 05 Mar 2009 00:26:05 GMT

Hi, Scott:
Wow, what a difference a comma and some parentheses can make in your program!

Basically, you will only get the COUNT or the N in an ACROSS scenario, unless you "cross" or "nest" your ACROSS variable with other report items or statistics. So, for example, if you wanted one item, it would be:
[pre] column groupvar acrossvar,onevar; [/pre]

if it was 2 items, it would be:
[pre] column groupvar acrossvar,(onevar twovar); [/pre]

and if it was 2 items and a computed item, it might be:
[pre]
column groupvar acrossvar,(onevar twovar calcvar);
define groupvar / group;
define acrossvar / across;
define onevar / sum;
define twovar / sum;
define calcvar / computed;
[/pre]

And, now that you've learned about onevar.sum and twovar.sum in a COMPUTE block, you have to learn something else about ACROSS usage. Proc Report has yet -another- way to reference across variables. So, let's say that, in the above fake example, that acrossvar has 3 possible values: XXX, YYY and ZZZ.

Essentially, you will have a report that looks like this:
[pre]

------------------------------ACROSSVAR------------------------------
---------XXX--------- ---------YYY--------- ---------ZZZ---------
groupvar onevar twovar calcvar onevar twovar calcvar onevar twovar calcvar
AAA 111 222 333 110 220 330 100 200 300
BBB 11 22 33 10 20 30 20 10 30
[/pre]

Now Proc Report can get easily confused. If you plan to calculate CALCVAR by adding ONEVAR and TWOVAR -- how does Proc REPORT know that you want the ONEVAR and TWOVAR for XXX versus the ONEVAR and TWOVAR for YYY??? So, in this case, the simple ONEVAR.SUM and TWOVAR.SUM will not make PROC REPORT happy.

Essentially, Proc Report goes through a setup phase when it gets started, and if you have any reference to ACROSS usage, then PROC REPORT figures out how many values you have for the across variables (in this case, 3), how many variables or items you have nested under the across variable (in this case, also 3) and how many items you have -before- the first ACROSS variable value (in this case -- 1 for groupvar). Then it assigns absolute column numbers to every one of the columns that comes from nesting within the ACROSS. Like this:
[pre]

------------------------------ACROSSVAR------------------------------
---------XXX--------- ---------YYY--------- ---------ZZZ---------
groupvar onevar twovar calcvar onevar twovar calcvar onevar twovar calcvar
AAA 111 222 333 110 220 330 100 200 300
BBB 11 22 33 10 20 30 20 10 30
| | | | | | | | | |
v v v v v v v v v v
first col _c2_ _c3_ _c4_ _c5_ _c6_ _c7_ _c8_ _c9_ _c10_
(these are now the "absolute" column names that REPORT wants you to use)
[/pre]

So, groupvar is conceptually column 1, but there's no confusion about how to reference it, so it counts as 1, but doesn't get an absolute name. However, starting with the column for ONEVAR under XXX, the column name internally becomes known as _c2_, and then TWOVAR under XXX becomes _c3_ and CALCVAR under XXX becomes _c4_, etc, etc. Even if you use NOPRINT to hide ONEVAR and TWOVAR, they still get absolute names assigned to them.

That means your compute block would look like:
[pre]
compute calcvar;
_c4_ = _c2_ + _c3_;
_c7_ = _c5_ + _c6_;
_c10_ = _c8_ + _c9_;
endcomp;
[/pre]

There have been some previous forum postings on ACROSS usage with Proc Report and there are some good user group papers about PROC REPORT. These are only a few references:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf
http://www2.sas.com/proceedings/sugi31/235-31.pdf
http://www2.sas.com/proceedings/sugi25/25/hands/25p148.pdf

cynthia

Re: Another Proc Report Question

Scottcom4 — Fri, 06 Mar 2009 05:51:20 GMT

Hi Cyntia.

Thank you so much. I had read countless documents on Proc Report and not one was as clear as your explanation.

Once again you have saved me.

Regards,
Scott

topic Re: Another Proc Report Question in SAS Procedures

Another Proc Report Question

Re: Another Proc Report Question

Re: Another Proc Report Question