Date:         Wed, 27 Nov 2002 16:15:39 GMT
Reply-To:     Tim Berryhill <tim@AARTWOLF.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tim Berryhill <tim@AARTWOLF.COM>
Organization: Concentric Internet Services
Subject:      Re: Help! WA aggregate implementation

Oscar, I have not used the Warehouse Administrator, and I do not know your
data structure, but I have a suggestion or two.  I assume you have a dataset
with an observation for each policy, rather than an observation for each
policy event.  I assume the dataset has variables like policy ID, Regional
office, underwriting date, effective date, manual cancel date, automatic
cancel date.  Those last two variables are important--the structure could
easily have a single cancel date with a separate reason variable, and then
my proposal would not work.

Read about PROC MEANS.  You can request an output dataset with statistics
for specific variables, so you can request the N statistic (number of
non-missing values) on underwriting date (your fact A), effective date (your
fact B), and manual cancel date (your fact C).

You can include a CLASS statement listing the variables for year, quarter,
month, regional office, service office, etc.  Use the NWAY option on the
PROC statement because your CLASS variables overlap, and the _TYPE_ variable
will let you select the observations with the level of detail you desire
(the observations summarized by year and zone office or by quarter and line
of business; whichever summary you want to present).

PROC REPORT can produce similar results.  MEANS is intended to produce a
dataset while REPORT is intended to produce printable results.  Which one to
use will probably come down to which one is easier to use in the Ware house
Administrator.

Good luck and happy learning,
Tim Berryhill
tim@aartwolf.com
PS: Is your company looking for excellent SAS programmers in SF?  I am
looking for a job here.

"Oscar" <oscar_terres@yahoo.com> wrote in message
news:1a9b41a1.0211262325.739bd3bd@posting.google.com...
> Hi,
>
> I work for an insurance company and for the accounts
> receivable data mart a report is needed and it seems
> to me that we should get multiple counts in a single
> fact, and this counts are related to events and
> conditions.
>
> For a given period of analysis (e.g. a month)
>
> Dimensions
> Time: Year, Quarter, Month
> Sales Organization: Regional office, Service office, Zone office
> Line of business: Auto, Damage, Health, Life
>
> Facts
> A. Number of policies by date of underwriting
> B. Number of policies by effective date
> C. Number of policies cancelled manually
> D. Number of policies cancelled automatically (without payment)
> E. Percentage of C / B
>
> For a granularity of policy, my questions are
>
> - Should I create a fact table with count fields with
>   1 or 0 to obtain the sum of this values at the
>   different aggregation levels?
> - Should I use conditions to define the values of the
>   fact table. For   example, if a policy was cancelled
>   on a given date, generate a 1 value?
> - Is it possible to apply an 'accumulating snapshot'
>   fact table with the different date keys (roles) and
>   simultaneously calculate appropriate counts for
>   each date? For example:
>
>   Underwriting date key
>   Effective date key
>   Cancellation date key
>   Cause of cancellation key
>   Policy key (degenerate dimension)
>   Number of underwritten policies
>   Number of policies to be effective (B)
>   Number of policies cancelled manually (C)
>   Number of policies cancelled automatically
>   Percentage of C / B
>
> By the way, I should implement this using SAS Warehouse
> Administrator.
>
> Thanks for your time and attention
>
> Oscar
>
> p.s. Direct e-mail is welcome too