topic Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR in SAS Programming

ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Fri, 05 Mar 2010 04:43:27 GMT

I'm new to SAS and I'm trying to figure out how to get someone else's code to run. After creating the SASDATA.AGG01_MEMBERSHIP dataset, the program leaves the macro and run an "error_check" macro which checks the status of syscc. If syscc is anything other than 0 or 4 the program stops. syscc is 1012. I looked at the SASDATA.AGG01_MEMBERSHIP dataset and it looks okay (all data appears to be there and it looks like valid data) so I thought that maybe the status of syscc was changing before the dataset was created, futher up in the code. I added "OBS=0 NOREPLACE" to the options and that is what generated the log code that follows. It was my understanding that these two options don't process data but are mainly checking syntax.

Here is the code that creates the datasets in the macro in the order that they appear:
data interim.membership_prior(sortedby=cust_seg_nbr indv_id mbr_mo);
set &mset
interim.member_&mbrmo;
by cust_seg_nbr indv_id mbr_mo;

data interim.membership_current(sortedby=cust_seg_nbr indv_id mbr_mo);
set &mset
interim.member_&mbrmo;
by cust_seg_nbr indv_id mbr_mo;

data interim.membership_prior(sortedby=cust_seg_nbr indv_id);
set interim.membership_prior;
by cust_seg_nbr indv_id mbr_mo;

data interim.membership_current(sortedby=cust_seg_nbr indv_id);
set interim.membership_current;
by cust_seg_nbr indv_id mbr_mo;

data interim.membership_prior(sortedby=cust_seg_nbr indv_id);
merge interim.membership_prior(in=a)
work.mths1;
by cust_seg_nbr indv_id;

data interim.membership_current(sortedby=cust_seg_nbr indv_id);
merge interim.membership_current(in=a)
work.mths2;
by cust_seg_nbr indv_id;

data interim.membership_prior(sortedby=period cust_seg_nbr indv_id);
merge interim.membership_prior(in=i)
work.client_data(in=d);
by cust_seg_nbr;

data interim.membership_current(sortedby=period cust_seg_nbr indv_id);
merge interim.membership_current(in=i)
work.client_data(in=d);
by cust_seg_nbr;

************************
Is the error happening on the data step above because it is not sorted by "period" but the dataset that follows is trying to sort by period? Does the "by" word actually sort the dataset by the variable that follows when the dataset is created? Does the "sortedby" statement not actually sort the data? Is it only informational, telling what order the dataset is supposed to be in? How does the proc sort fit in. Isn't it also used to sort a dataset?
************************

data sasdata.agg01_membership(sortedby=period cust_seg_nbr indv_id compress=yes);
set interim.membership_prior
interim.membership_current;
by period cust_seg_nbr indv_id;

ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR.
ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_CURRENT.
23 The SAS System 07:25 Thursday, March 4, 2010

ERROR: Variable period not found.
ERROR: Invalid value for the SORTEDBY option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
WARNING: The data set SASDATA.AGG01_MEMBERSHIP may be incomplete. When this step was stopped there were 0 observations and 0
variables.
WARNING: Data set SASDATA.AGG01_MEMBERSHIP was not replaced because this step was stopped.

Thanks in advance,

DanD

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

Flip — Fri, 05 Mar 2010 13:48:25 GMT

Your log statesd that PERIOD is not on either dataset. Have you checked these datasets to insure that PERIOD is there? I don't see PERIOD anywhere else in your code.

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

sbb — Fri, 05 Mar 2010 13:51:33 GMT

The SAS variable named PERIOD does not exist, as revealed in the SAS error message. You will need to trace your SAS program, preferably using a SAS log with all code revealed, and also use SAS PROC CONTENTS to determine what code/step assigns a PERIOD variable, if at all.

This is pretty basic SAS 101 processing diagnostics, given the straightforward error message you received.

Also, strongly recommend that future post-replies on this thread include "complete" SAS log with your SAS code expanded for accurate and useful forum feedback.

Scott Barry
SBBWorks, Inc.

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Fri, 05 Mar 2010 14:02:29 GMT

> Your log statesd that PERIOD is not on either
> dataset. Have you checked these datasets to insure
> that PERIOD is there? I don't see PERIOD anywhere
> else in your code.

PERIOD only shows up in the "sortedby" line. But that doesn't mean that the dataset is actually sorted by PERIOD. Correct?

What if I change the dataset before the sasdata.agg01_membership is created from this:
data interim.membership_current(sortedby=period cust_seg_nbr indv_id);
merge interim.membership_current(in=i)
work.client_data(in=d);
by cust_seg_nbr;

to this:
data interim.membership_current(sortedby=period cust_seg_nbr indv_id);
merge interim.membership_current(in=i)
work.client_data(in=d);
by period cust_seg_nbr indv_id;

where I change the "by" line. Do you think that will solve the problem?

Thanks,

DanD

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

Flip — Fri, 05 Mar 2010 14:05:59 GMT

None of that matters at this point. PERIOD DOES NOT EXIST. Where is the PERIOD variable that you are stating your input datasets are sorted by. You can't have a BY statement referencing a variable that does not exist.

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Fri, 05 Mar 2010 14:07:44 GMT

> The SAS variable named PERIOD does not exist, as
> revealed in the SAS error message. You will need to
> trace your SAS program, preferably using a SAS log
> with all code revealed, and also use SAS PROC
> CONTENTS to determine what code/step assigns a PERIOD
> variable, if at all.
>
> This is pretty basic SAS 101 processing diagnostics,
> given the straightforward error message you
> received.
>
> Also, strongly recommend that future post-replies on
> this thread include "complete" SAS log with your SAS
> code expanded for accurate and useful forum
> feedback.
>
> Scott Barry
> SBBWorks, Inc.

Here is the entire log. I didn't want to post it before because it was a little long. Can I go back to the previous dataset and correct the problem when that dataset is created just using the "by" line? Or do I need to use a PROC SORT on the previous dataset to change the sort before creating the sasdata.agg01_membership dataset?

Thanks,

DanD

NOTE: PROCEDURE PRINTTO used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 19
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

6940
6941
6942
6943 /**************************************************************
6944 * Call the Macros to build the Aggs
6945 **************************************************************/
6946 %macro build_aggs;
6947
6948 /*****************************************************************
6949 * Print Current Client's parms in the SAS Log file.
6950 *****************************************************************/
6951 %Put ************************************************************;
6952 %Put ;
6953 %Put Prior Begin Date : &PriorBegDt;
6954 %Put Prior End Date : &PriorEndDt;
6955 %Put Current Begin Date : &CurrBegDt;
6956 %Put Current End Date : &CurrEndDt;
6957 %Put ;
6958 %Put Customer Segs : &CustSegs;
6959 %Put ;
6960 %Put **************************************************************;
6961 %Put;
6962
6963 /**************************************************************
6964 * Macro to pull the membership data for the last month in the
6965 * current reporting period to identify the clients that
6966 * the report should run for
6967 **************************************************************/
6968 %Put ********************************************************;
6969 %Put Begin Account ID Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
6970 %Put ********************************************************;
6971 /* %identify_key_accounts;
6972 %error_check(section=Identify_Key_Accounts);
6973 */
6974
6975 %macro call_agg_builds;
6976
6977 %Put ********************************************************;
11 The SAS System 07:25 Thursday, March 4, 2010

6978 %Put Begin Membership Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
6979 %Put ********************************************************;
6980 %agg01_membership_extract;
6981 %error_check(section=agg01_membership_extract);
6982
6983 %Put ********************************************************;
6984 %Put Begin CSP Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
6985 %Put ********************************************************;
6986 %agg16_csp_engagement_extract;
6987 %error_check(section=agg16_csp_engagement_extract);
6988
6989 %Put ********************************************************;
6990 %Put Begin Closure Reason Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
6991 %Put ********************************************************;
6992 %agg09_closure_reasons_1_extract;
6993 %error_check(section=agg09_closure_reasons_1_extract);
6994
6995 %Put ********************************************************;
6996 %Put Begin HeltheNotes Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
6997 %Put ********************************************************;
6998 %agg17_hen_extract;
6999 %error_check(section=agg17_hen_extract);
7000
7001 %Put ********************************************************;
7002 %Put Begin Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7003 %Put ********************************************************;
7004 %agg02_engagement_extract;
7005 %error_check(section=agg02_engagement_extract);
7006
7007 %Put ********************************************************;
7008 %Put Begin VDI/eSync Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7009 %Put ********************************************************;
7010 %agg04_vdi_extract;
7011 %error_check(section=agg04_vdi_extract);
7012
7013 %Put ********************************************************;
7014 %Put Begin Referral Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7015 %Put ********************************************************;
7016 %agg11_referral_source_extract;;
7017 %error_check(section=agg11_referral_source_extract);
7018
7019 %Put ********************************************************;
7020 %Put Begin Reminder Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7021 %Put ********************************************************;
7022 %agg18_reminder_mailings;;
7023 %error_check(section=agg18_reminder_mailings);
7024
7025
7026 %mend call_agg_builds;
7027 %call_agg_builds;
7028
7029 /**************************************************************
7030 * Macro to Join the Membership data to the aggs to add the
7031 * criteria data
7032 **************************************************************/
7033 %macro join_membership_add_indexes;
7034
7035 /**************************************************************
12 The SAS System 07:25 Thursday, March 4, 2010

7036 * Join each of the Aggs to the Membership data
7037 * These are the fields where criteria may be applied
7038 **************************************************************/
7039 proc sort nodupkey data=sasdata.agg01_membership(keep=period indv_id cust_seg_nbr rel_cd age_id gdr_cd
7039 ! sbscr_empmt_sts_cd
7040 prdct_cd prdct_cd_ky ben_strct_1_cd ben_strct_2_cd
7040 ! franch_1_cd franch_2_cd
7041 subgrp_1_cd subgrp_2_cd fund_arng_cd mkt_seg_cd mbr_zip_cd
7042 elig_mths
7043 fully_insured key_account natl_acct aso uhcfi public_sector
7043 ! acec sh
7044 clinicalprogramgroup channel fundingarrangement
7045 normgroup dm_ind mkt_seg_grp_cd indus_prdct_cd maj_mkt_nbr
7045 ! mkt_cd
7046 data_source_cd hmo_acct_div_nbr co_cd)
7047 out=work.mbrdata(rename=(period=mperiod indv_id=mindv_id));
7048 by period indv_id cust_seg_nbr;
7049 run;
7050
7051 %macro join_agg1(ds);
7052
7053 proc sort data=interim.&ds;
7054 by period indv_id;
7055 run;
7056
7057 proc sql;
7058 create table sasdata.&ds(drop=mperiod mindv_id) as
7059 select a.*,
7060 b.*
7061 from interim.&ds a,
7062 work.mbrdata b
7063 where a.period = b.mperiod
7064 and a.indv_id = b.mindv_id;
7065 quit;
7066
7067 proc sort data=sasdata.&ds;
7068 by cust_seg_nbr period indv_id;
7069 run;
7070
7071 proc datasets library=sasdata;
7072 modify &ds;
7073 index create cust_seg_nbr;
7074 index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
7075 index create prd_indv_key=(period indv_id);
7076 run;
7077 quit;
7078
7079 %mend join_agg1;
7080
7081 %join_agg1(agg16_csp_vdi);
7082 %join_agg1(agg9_1_ods);
7083 %join_agg1(agg04_opportunities);
7084 %join_agg1(agg11_referral_source);
7085 %join_agg1(agg17_hen_member);
7086 %join_agg1(agg17_hen_provider);
7087 %join_agg1(agg18_reminder_mailings);
7088 %join_agg1(agg2_engagement);
7089
13 The SAS System 07:25 Thursday, March 4, 2010

7090 /**************************************************************
7091 * Add index to membership
7092 **************************************************************/
7093 %macro index_membership;
7094 proc datasets library=sasdata;
7095 modify agg01_membership;
7096 index create cust_seg_nbr;
7097 index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
7098 index create prd_indv_key=(period indv_id);
7099 run;
7100 quit;
7101 %mend index_membership;
7102 %index_membership;
7103
7104 %mend join_membership_add_indexes;
7105 %join_membership_add_indexes;
7106 %error_check(section=join_membership_add_indexes);
7107
7108
7109 %mend build_aggs;
7110 %build_aggs;
************************************************************

Prior Begin Date : '07-JUL-2009'
Prior End Date : '31-DEC-2009'
Current Begin Date : '07-JUL-2009'
Current End Date : '31-DEC-2009'

Customer Segs : '000228542'

**************************************************************

********************************************************
Begin Account ID Processing. Date: 03/04/2010 Time: 7:25:51
********************************************************
********************************************************
Begin Membership Processing. Date: 03/04/2010 Time: 7:25:51
********************************************************

NOTE: Deleting INTERIM.MEMBERSHIP_PRIOR (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 1
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting INTERIM.MEMBERSHIP_CURRENT (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
14 The SAS System 07:25 Thursday, March 4, 2010

user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 1
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 371k
Page Faults 0
Page Reclaims 19
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

rprd=
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

15 The SAS System 07:25 Thursday, March 4, 2010

NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200908.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 29 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
Memory 1040k
Page Faults 4
Page Reclaims 242
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200908.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 29 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1040k
Page Faults 0
Page Reclaims 176
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200909.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
16 The SAS System 07:25 Thursday, March 4, 2010

user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 1391k
Page Faults 4
Page Reclaims 356
Page Swaps 0
Voluntary Context Switches 5
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200909.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 0
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200910.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 4
17 The SAS System 07:25 Thursday, March 4, 2010

Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 5
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200910.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 0
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200911.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 4
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 5
Involuntary Context Switches 7
18 The SAS System 07:25 Thursday, March 4, 2010

Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200911.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 0
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200912.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
Memory 1390k
Page Faults 4
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0

19 The SAS System 07:25 Thursday, March 4, 2010

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set INTERIM.MEMBER_200912.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 29 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1390k
Page Faults 0
Page Reclaims 274
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

NOTE: Table WORK.MTHS1 created, with 0 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 775k
Page Faults 3
Page Reclaims 143
Page Swaps 0
Voluntary Context Switches 5
Involuntary Context Switches 11
Block Input Operations 0
Block Output Operations 0

NOTE: Table WORK.MTHS2 created, with 0 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 775k
Page Faults 0
Page Reclaims 86
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 28 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
20 The SAS System 07:25 Thursday, March 4, 2010

system cpu time 0.00 seconds
Memory 1040k
Page Faults 0
Page Reclaims 164
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 28 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1040k
Page Faults 0
Page Reclaims 164
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set WORK.MTHS1.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 30 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1736k
Page Faults 0
Page Reclaims 296
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.MTHS1 (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
21 The SAS System 07:25 Thursday, March 4, 2010

Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set WORK.MTHS2.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 30 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
Memory 1736k
Page Faults 0
Page Reclaims 294
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.MTHS2 (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Input data set is empty.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.CLIENT_DATA has 0 observations and 9 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 258k
Page Faults 5
Page Reclaims 70
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

22 The SAS System 07:25 Thursday, March 4, 2010

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 0 observations read from the data set WORK.CLIENT_DATA.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 0 observations and 40 variables.
WARNING: Data set INTERIM.MEMBERSHIP_PRIOR was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1635k
Page Faults 0
Page Reclaims 324
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 9
Block Input Operations 0
Block Output Operations 0

NOTE: There were 0 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 0 observations read from the data set WORK.CLIENT_DATA.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 0 observations and 42 variables.
WARNING: Data set INTERIM.MEMBERSHIP_CURRENT was not replaced because of NOREPLACE option.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1602k
Page Faults 0
Page Reclaims 220
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.CLIENT_DATA (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR.
ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_CURRENT.
23 The SAS System 07:25 Thursday, March 4, 2010

ERROR: Variable period not found.
ERROR: Invalid value for the SORTEDBY option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
WARNING: The data set SASDATA.AGG01_MEMBERSHIP may be incomplete. When this step was stopped there were 0 observations and 0
variables.
WARNING: Data set SASDATA.AGG01_MEMBERSHIP was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 877k
Page Faults 2
Page Reclaims 172
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 6
Block Input Operations 0
Block Output Operations 0

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 103k
Page Faults 0
Page Reclaims 8
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 105k
Page Faults 0
Page Reclaims 8
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Fileref MAILOUT has been deassigned.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 103k
Page Faults 0
24 The SAS System 07:25 Thursday, March 4, 2010

Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

********************************************************
Begin CSP Engagement Processing. Date: 03/04/2010 Time: 7:25:51
********************************************************

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 106k
Page Faults 0
Page Reclaims 8
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

sdtkey1 is
edtkey1 is
sdtkey2 is
edtkey2 is
NOTE: Table INTERIM.CSP_TOP_CASES created, with 0 rows and 12 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.02 seconds
system cpu time 0.01 seconds
Memory 711k
Page Faults 15
Page Reclaims 728
Page Swaps 0
Voluntary Context Switches 27
Involuntary Context Switches 22
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 21k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
25 The SAS System 07:25 Thursday, March 4, 2010

Block Output Operations 0

NOTE: The data set INTERIM.CSP_TOP_CASES has 0 observations and 13 variables.
WARNING: Data set INTERIM.CSP_TOP_CASES was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 138k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0

ERROR: Variable SRC_PERSON_ID not found.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 21k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: Column src_person_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_person_id could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 54k
Page Faults 7
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

ERROR: File INTERIM.CSP_TOP_CASES_OUT.DATA does not exist.
26 The SAS System 07:25 Thursday, March 4, 2010

ERROR: No data set open to look up variables.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CASES may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: Compression was disabled for data set WORK.CASES because compression overhead would increase the size of the data set.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 21k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

ERROR: Variable cse_create_dt is not on file WORK.CASES.

NOTE: Compression was disabled for data set WORK.CASES_P because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CASES_P may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 132k
Page Faults 0
Page Reclaims 13
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

ERROR: Variable cse_create_dt is not on file WORK.CASES.

NOTE: Compression was disabled for data set WORK.CASES_C because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CASES_C may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 132k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

27 The SAS System 07:25 Thursday, March 4, 2010

ERROR: Table WORK.CASES_P doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 66k
Page Faults 3
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

ERROR: Table WORK.CASES_C doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
ERROR: Column src_cse_id could not be found in the table/view identified with the correlation name A.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 66k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
7110:214 7110:75 7110:209 7110:75 7110:209 7110:75 7110:209 7110:75
WARNING: The variable create_dt in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The data set WORK.CASES has 0 observations and 4 variables.
WARNING: Data set WORK.CASES was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 147k
Page Faults 1
Page Reclaims 12
Page Swaps 0
28 The SAS System 07:25 Thursday, March 4, 2010

Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
7110:7 7110:42 7110:89 7110:119 7110:154 7110:201 7110:9 7110:44 7110:91 7110:138 7110:173
NOTE: The data set INTERIM.CSP_CASES has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
Memory 143k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
29 The SAS System 07:25 Thursday, March 4, 2010

system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: The data set WORK.OPPORTUNITIES has 0 observations and 0 variables.
NOTE: Compression was disabled for data set WORK.OPPORTUNITIES because compression overhead would increase the size of the data set.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 56k
Page Faults 12
Page Reclaims 116
Page Swaps 0
Voluntary Context Switches 3
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0

ERROR: Table WORK.OPPORTUNITIES doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: Column SRC_CSE_ID could not be found in the table/view identified with the correlation name SNP.
ERROR: Column SRC_CSE_ID could not be found in the table/view identified with the correlation name SNP.
ERROR: Column VLP_SRC_CSE_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column VLP_SRC_CSE_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column INDV_ID could not be found in the table/view identified with the correlation name SNP.
ERROR: Column VLP_PGM_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column VLP_SRC_CSE_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column CUST_SEG_NBR could not be found in the table/view identified with the correlation name SNP.
ERROR: Column CSE_CREATE_DT could not be found in the table/view identified with the correlation name SNP.
ERROR: Column SRC_CSE_ID could not be found in the table/view identified with the correlation name SNP.
ERROR: Column MBR_OPP_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column OPP_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column OPP_STATUS_ID could not be found in the table/view identified with the correlation name CRS.
ERROR: Column STS_RSN_ID could not be found in the table/view identified with the correlation name CRS.
30 The SAS System 07:25 Thursday, March 4, 2010

ERROR: Column OPP_STATUS_DTS could not be found in the table/view identified with the correlation name CRS.
ERROR: Column OPP_CREATE_DT could not be found in the table/view identified with the correlation name CRS.
ERROR: Column DOBTS could not be found in the table/view identified with the correlation name CRS.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 66k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: File INTERIM.CSP_VDI.DATA does not exist.
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: ORACLE prepare error: ORA-00936: missing expression. SQL statement: SELECT CHS.SRC_CSE_ID FROM OPTUMODS.F_CH_SNAPSHOT CHS,
OPTUMODS.D_PATIENT_ACCT PA, OPTUMODS.D_PERSON PERSON, OPTUMODS.VW_D_PROGRAM_TYPE PRG WHERE CHS.D_PAT_ACCT_KEY =
PA.D_PAT_ACCT_KEY AND CHS.D_PRG_TYPE_KEY = PRG.D_PRG_TYPE_KEY AND PA.D_PERSON_KEY = PERSON.D_PERSON_KEY AND
31 The SAS System 07:25 Thursday, March 4, 2010

PRG.SRC_PRG_TYPE_CD IN ('41','44','45','46','47','55','56') AND (CHS.D_ASOF_DT_KEY BETWEEN and OR CHS.D_ASOF_DT_KEY BETWEEN
and ).
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 139k
Page Faults 2
Page Reclaims 32
Page Swaps 0
Voluntary Context Switches 24
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0

ERROR: File INTERIM.AGG16_SNAP.DATA does not exist.
ERROR: No data set open to look up variables.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Table INTERIM.AGG16_CASE_ACTIVITY created, with 0 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 10.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
Memory 694k
Page Faults 0
Page Reclaims 116
Page Swaps 0
Voluntary Context Switches 118
Involuntary Context Switches 11
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
32 The SAS System 07:25 Thursday, March 4, 2010

system cpu time 0.00 seconds
Memory 21k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: File INTERIM.AGG16_SNAP.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 50k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

33 The SAS System 07:25 Thursday, March 4, 2010

ERROR: File INTERIM.CSP_VDI_CASE_ACTIVITY.DATA does not exist.
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CSE_EN may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: Compression was disabled for data set WORK.CSE_EN because compression overhead would increase the size of the data set.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 21k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Compression was disabled for data set WORK.CSE_EN because compression overhead would increase the size of the data set.
NOTE: The data set WORK.CSE_EN has 0 observations and 2 variables.
WARNING: Data set WORK.CSE_EN was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
Memory 124k
Page Faults 0
Page Reclaims 12
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

ERROR: File INTERIM.CSP_VDI.DATA does not exist.
ERROR: Table WORK.CSE_EN doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 50k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

34 The SAS System 07:25 Thursday, March 4, 2010

NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Sw

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

Flip — Fri, 05 Mar 2010 14:17:23 GMT

ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR.
ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_CURRENT

PERIOD does not exist. Yes you must sort by PERIOD to use a BY statement in a merge. Also the log you posted states you have no data. Is this by design to check syntax, or are you ignoring the early warnings and looking at the later error message?

Start at the top of your log, and resolve each warning or error in turn.
Look at the two datasets you are refering to and see why PERIOD is not in them.

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Fri, 05 Mar 2010 14:29:20 GMT

> None of that matters at this point. PERIOD DOES NOT
> EXIST. Where is the PERIOD variable that you are
> stating your input datasets are sorted by. You can't
> have a BY statement referencing a variable that does
> not exist.

I see what you mean Flip. I don't see where the Period variable come from either. I'll try to find that out and get it in the data. After that can I just change the sort on the dataset previous to the creation of the sasdata.agg01_membership?

Thanks,

DanD

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Fri, 05 Mar 2010 14:47:11 GMT

> ERROR: BY variable period is not on input data set
> INTERIM.MEMBERSHIP_PRIOR.
> ERROR: BY variable period is not on input data set
> INTERIM.MEMBERSHIP_CURRENT
>
> PERIOD does not exist. Yes you must sort by PERIOD
> to use a BY statement in a merge. Also the log you
> posted states you have no data. Is this by design to
> check syntax, or are you ignoring the early warnings
> and looking at the later error message?
>
> Start at the top of your log, and resolve each
> warning or error in turn.
> Look at the two datasets you are refering to and see
> why PERIOD is not in them.

Yes, there is not data by design. I didn't get any helpful error messages originally so I added the OBS=0 and NOREPLACE to the options statement and the error messages you see are the result. This is supposed to be a program that has been run successfully but from all of the error messages that I see, I don't know how it could have been.

Thanks Flip,

DanD

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

sbb — Fri, 05 Mar 2010 16:15:25 GMT

To the OP - you have a SAS application problem now, regardless of whether it worked previously. You are asking the forum to judge whether you can or cannot simply add PERIOD to one (or more) of your SAS data set tables, with any confidence that your problem will be solved? Does that make good program diagnosis sense to you? It doesn't to me.

Strongly suggest you circle-back to find the author/owner and work with those individual(s) to ascertain what's changed since the application last ran successfully, make any necessary changes, test those changes to a successful completion, and then let's hear back.

Consider that you are asking for guidance with an unknown quantity at this point in the forum post-thread.

Scott Barry
SBBWorks, Inc.

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

Peter_C — Sat, 06 Mar 2010 09:44:13 GMT

DanD

> syscc was changing before the dataset was created,
> futher up in the code. I added "OBS=0 NOREPLACE" to
> the options and that is what generated the log code
> that follows. It was my understanding that these two
> options don't process data but are mainly checking
> syntax.
.
unfortunately, this pair of options may have caused subsequent error conditions.

For example, OBS=0 would prevent proc sql filling a macro variable list of dataset variables. and sometimes, for a program to work properly, a dataset needs to be replaced - with different columns.

to investigate this kind of problem, I think you need to insert something to report the value of SYSCC at many key points in your program, backup all required output data, and re-run without applying the NOREPLACE and OBS=0

peterC

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Mon, 08 Mar 2010 13:55:02 GMT

> To the OP - you have a SAS application problem now,
> regardless of whether it worked previously. You are
> asking the forum to judge whether you can or cannot
> simply add PERIOD to one (or more) of your SAS data
> set tables, with any confidence that your problem
> will be solved? Does that make good program
> diagnosis sense to you? It doesn't to me.
>
> Strongly suggest you circle-back to find the
> author/owner and work with those individual(s) to
> ascertain what's changed since the application last
> ran successfully, make any necessary changes, test
> those changes to a successful completion, and then
> let's hear back.
>
> Consider that you are asking for guidance with an
> unknown quantity at this point in the forum
> post-thread.
>
> Scott Barry
> SBBWorks, Inc.

I have turned the problem over to the people who gave me the code and they're working on it. However, since I'm new to SAS I still want to do some troubleshooting so I can learn.

Since I know very little about SAS I didn't know if just trying to remove the PERIOD variable from the sort or adding it to the query would be the solution or not. I've read some things in articles/forum posts that said that sometimes solving the first problem also fixes subsequent problems even though the subsequent problems seemed unrelated. Since I didn't know and I'm learning I thought that it was worth a try.

After trying the OBS and NOREPLACE options I was surprised to see so many other errors from a program that supposedly was tested and worked. I didn't know if the errors that the options were showing were accurate or not. However, after reading Peter's post after yours it looks like those options can create their own problems.

Thanks for your help,

DanD

Re: ERROR: BY variable period is not on input data set INTERIM.MEMBERSHIP_PRIOR

DanD — Mon, 08 Mar 2010 14:59:03 GMT

> DanD
>
> > syscc was changing before the dataset was created,
> > futher up in the code. I added "OBS=0
> NOREPLACE" to
> > the options and that is what generated the log
> code
> > that follows. It was my understanding that these
> two
> > options don't process data but are mainly checking
> > syntax.
> .
> unfortunately, this pair of options may have caused
> subsequent error conditions.
>
> or example, OBS=0 would prevent proc sql filling a
> macro variable list of dataset variables. and
> sometimes, for a program to work properly, a dataset
> needs to be replaced - with different columns.
>
> to investigate this kind of problem, I think you need
> to insert something to report the value of SYSCC at
> many key points in your program, backup all required
> output data, and re-run without applying the
> NOREPLACE and OBS=0
>
> eterC
>

I did try that Peter. In the agg01_membership_extract file I put some output for the syscc variable. I thought that maybe it was getting reset from 0 somewhere higher up in the file. I had one output line right at the beginning of the file and sure enough, syscc was 1012 as soon as the program entered that file/macro.

So, I went back to the first file, agg_build_driver and put output statements in there. However, the first output statement that prints to the log file is the fourth one. I don't understand why the first 3 won't print. By the time the program gets to the fourth output statement, syscc is already 1012.

I'll show the code and log below. Maybe someone can tell me why the first 3 output statements won't print.

Thanks,

DanD

Code:

options compress=yes;

/* options OBS=0 NOREPLACE; */

/**************************************************************
* Key Accounts Agg Extract Driver
**************************************************************/
/* Your email id you would like to be notified when complete */
%let emailcomplete = dan.donahue@optumhealth.com;

/* Prior Reporting Period - Key Accounts this is the YTD */
%let PriorBegDt = '07-JUL-2009';
%let PriorEndDt = '31-DEC-2009';

/* Current Reporting Period - Key Accounts this is the Current Quarter */
%let CurrBegDt = '07-JUL-2009';
%let CurrEndDt = '31-DEC-2009';

%let priorpaiddt = &PriorEndDt;
%let currpaiddt = &CurrEndDt;

%let prevprd = PRIOR;
%let currprd = CURRENT;

%let delete_interim_data = NO;

/* %let database = /SASInt/optum/vmm/shared/data/car/key_accts; */
%let database = /SASInt/optum/csg/users/ddonah6/work/cc;

%let odsuser = optumuser;
%let odspass = optumuser;
%let odspath = @URNPR1;

%let crmuser = crmro;
%let crmpass = crmro0918;
%let crmpath = @adwprd01;

%let mnocpath = @urndv1;

%let CustSegs = '000228542';

/**************************************************************
* Clean up some options
**************************************************************/

/**************************************************************
* These will hold the table names created for the ODS queries
**************************************************************/
%let person_table_name = OPTUMODS.D_PERSON;
%let person_table_only = D_PERSON;
%let indvid_table_name = ;
%let indvid_table_only = ;

/**************************************************************
* Create directory macro
**************************************************************/
%macro make_directory(check_path);
%if %sysfunc(FileExist(&check_path)) = 0 %then %do;
%sysexec(mkdir -p &check_path);
%end;
%sysexec(chmod 777 &check_path);
%mend make_directory;

data _null_;
cd = &CurrEndDt.d;
length dn $7;
dn = 'm' || substr(put(cd,yymmddn8.),1,6);
call symput('data_month',strip(dn));
%PUT "syscc agg0 variable is : &syscc";
run;

%let sasdatapath = &database/&data_month;
%make_directory(&sasdatapath);

%let interimpath = &database/&data_month/interim;
%make_directory(&interimpath);

%let buildlogpath = &database/&data_month/logs;
%make_directory(&buildlogpath);

%Put ************************************************************;
%Put ;
%Put syscc syscc1 variable is : &syscc;
%Put ;
%Put **************************************************************;
%Put;

/**************************************************************
* Assign common libraries
**************************************************************/
libname sasdata "&sasdatapath";
libname interim "&interimpath";

libname ppdata '/SASInt/optum/rad/intra_shared/ppdata' access=readonly;
libname srcdata '/SASInt/optum/rad/intra_shared/srcdata' access=readonly;
libname landing '/SASInt/optum/rad/intra_shared/landing' access=readonly;
libname covrprod '/SASInt/optum/vmm/shared/data/covr/prod' access=readonly;
libname otool '/SASInt/optum/vmm/projects/ordertool/prod/data' access=readonly;
libname hen '/SASInt/optum/vmm/shared/data/covr/hn' access=readonly;

/* National Reminders, TRS, Eng Ind Table, Template data sets */
libname carfiles '/SASInt/optum/rad/intra_shared/car/prod/sasfiles' access=readonly;

%let codepath = /SASInt/optum/vmm/shared/data/car/key_accts/agg_code/includelib;

/**************************************************************
* Do some date and path checks
* If date set to AUTO, set to most recent complete qtr
* If directory does not exist, create it for the last month in the current period
**************************************************************/
%macro check_period_dates;

%let err = NO;
data _null_;
pbd = &priorbegdt.d;
ped = &priorenddt.d;
cbd = &currbegdt.d;
ced = &currenddt.d;

err = 0;
if pbd ne intnx('year',pbd,0,'b') then DO;
err = 1;
%PUT "ERROR : &err";
end;
if ped ne ced then DO;
err = 1;
%PUT "ERROR : &err";
end;
dd = intnx('quarter',cbd,0,'e')
if ced ne dd then DO;
err = 1;
%PUT "ERRORdd : &dd";
%PUT "ERRORced : &ced";
end;
if ced ne intnx('quarter',ced,0,'e') then err = 1;
if cbd ne intnx('quarter',cbd,0,'b') then err = 1;

if err then do;
call symput('err','YES');
put '***** DATES ARE NOT CORRECT - RECHECK AND RE-SUBMIT ***';
end;
run;

%if &err = YES %then %do;
filename mailout
email
to = "&emailcomplete"
emailid = "&emailcomplete"
sender = "&emailcomplete"
subject = "CESR Agg Creation Date Error - Action Required";

data _null_;
file mailout;
put 'CESR Agg Creation Date Error';
put 'Verify Dates and Re-Submit';
put "&prevprd Period Start Date : &priorbegdt";
put "&prevprd Period End Date : &priorenddt";
put "&currprd Period Start Date : &currbegdt";
put "&currprd Period Start Date : &currenddt";
run;

filename mailout clear;

data _null_;
if "&err" = "YES" then do;
abort abend;
end;
run;

%end;

%mend check_period_dates;
%check_period_dates;

%Put ************************************************************;
%Put ;
%Put syscc syscc2 variable is : &syscc;
%Put ;
%Put **************************************************************;
%Put;

/**************************************************************
* Include the code files
**************************************************************/
%Include "/SASInt/optum/csg/users/ddonah6/work/agg01_membership_extract.sas";
%Include "&codepath/agg9_1_ods_extract.sas";
%Include "&codepath/agg16_csp_vdi_extract.sas";
%Include "&codepath/agg02_engagement_extract.sas";

%Include "&codepath/agg04_vdi_extract.sas";
%Include "&codepath/agg04_gaps_extract.sas";
%Include "&codepath/agg11_referral_source_extract.sas";
%Include "&codepath/agg17_hen_extract.sas";
%Include "&codepath/agg18_reminder_mailings.sas";

%Include "&codepath/identify_key_accounts_all.sas";

/**************************************************************
* Do some work section
**************************************************************/

/**************************************************************
* Make sure CustSeg list includes 6 and 9 byte version and
* that is is single quoted
**************************************************************/
%macro check_custsegs;
%if not %index(?%quote(&CustSegs)?,??) %then %do;

data custseglist;
length seg custseg $20 seglist $30000 seg9 seg6 $20;
keep custseg;

seglist = "&CustSegs";
seglist = compress(seglist,"'");
seglist = compress(seglist,'"');
seglist = compress(seglist,' ');

do x = 1 to 10000;
seg = scan(seglist,x,',');
seg = strip(seg);

if seg = '' then leave;

custseg = strip(seg);
output;

if index(seg,'|') then do;
custseg = strip(seg);
output;
end;
else do;
seg9 = reverse(substr(reverse(cats('000000000',seg)),1,9));
seg6 = reverse(substr(reverse(cats('000000000',seg)),1,6));
custseg = strip(seg9);
output;
custseg = strip(seg6);
output;
end;
end;
run;

proc sort nodupkey data=custseglist;
by custseg;
run;

data _null_;
set custseglist end=lastobs;
length seglist $30000;
retain seglist;
seglist = strip(seglist) || "'" || strip(custseg) || "'";
if lastobs then call symput('custsegs',strip(seglist));
else seglist = strip(seglist) || ",";
run;

proc delete data=custseglist;
run;

%end;

%mend check_custsegs;
%check_custsegs;

%Put ************************************************************;
%Put ;
%Put syscc syscc3 variable is : &syscc;
%Put ;
%Put **************************************************************;
%Put;

/**************************************************************
* Macro to call after each Agg build macro
* It Checks the Max RC and clears the work library
**************************************************************/
%macro error_check(clear_work=YES,section=,l1=,l2=,l3=,l4=,l5=);

data _null_;
mcc = "&syscc";
put mcc=;
run;

%if (&syscc = 0 or &syscc = 4) %then;
%else %do;
filename mailout
email
to = "&emailcomplete"
emailid = "&emailcomplete"
sender = "&emailcomplete"
subject = "CESR Aggregate Build Error In &section - Action Required";

data _null_;
file mailout;
put "CESR Aggregate Build Error In &section";
put 'Check the Log File and Correct the Error and Re-Submit';
if "&l1" ne "" then put "&l1";
if "&l2" ne "" then put "&l2";
if "&l3" ne "" then put "&l3";
if "&l4" ne "" then put "&l4";
if "&l5" ne "" then put "&l5";
run;
filename mailout clear;

data _null_;
abort abend;
stop;
run;

%end;

%mend error_check;

/**************************************************************
* Common macro that can be called to filter the data set passed in by period indv_id
* In the prod code this macro will be empty as we pull for the full book
**************************************************************/
%macro membership_filter(ds);

%mend membership_filter;
/*
%Put "syscc agg3a variable is : &syscc";
*/
/**************************************************************
* Set printto log
**************************************************************/
%macro set_print_to_log;

/**************************************************************
* Create a DateTime var to use for the Log
**************************************************************/
%let rundttm = ;
data _null_;
length dt $9 tm $7 dttm $16;
dt = 'd' || put(today(),yymmddn8.);
tm = compress(put(time(),time8.),' :');
if length(tm) = 5 then tm = '0' || strip(tm);
tm = 't' || strip(tm);
dttm = strip(dt) || strip(tm);
call symput('rundttm',trim(dttm));
run;

proc printto log="&buildlogpath/agg_build_&data_month._&rundttm..log" new;
run;

%mend set_print_to_log;
%set_print_to_log;
%Put ************************************************************;
%Put ;
%Put syscc syscc4 variable is : &syscc;
%Put ;
%Put **************************************************************;
%Put;

/**************************************************************
* Call the Macros to build the Aggs
**************************************************************/
%macro build_aggs;

/*****************************************************************
* Print Current Client's parms in the SAS Log file.
*****************************************************************/
%Put ************************************************************;
%Put ;
%Put Prior Begin Date : &PriorBegDt;
%Put Prior End Date : &PriorEndDt;
%Put Current Begin Date : &CurrBegDt;
%Put Current End Date : &CurrEndDt;
%Put ;
%Put Customer Segs : &CustSegs;
%Put ;
%Put **************************************************************;
%Put;

/**************************************************************
* Macro to pull the membership data for the last month in the
* current reporting period to identify the clients that
* the report should run for
**************************************************************/
%Put ********************************************************;
%Put Begin Account ID Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
/* %identify_key_accounts;
%error_check(section=Identify_Key_Accounts);
*/

%macro call_agg_builds;

%Put ********************************************************;
%Put Begin Membership Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg01_membership_extract;
%error_check(section=agg01_membership_extract);

%Put ********************************************************;
%Put Begin CSP Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg16_csp_engagement_extract;
%error_check(section=agg16_csp_engagement_extract);

%Put ********************************************************;
%Put Begin Closure Reason Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg09_closure_reasons_1_extract;
%error_check(section=agg09_closure_reasons_1_extract);

%Put ********************************************************;
%Put Begin HeltheNotes Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg17_hen_extract;
%error_check(section=agg17_hen_extract);

%Put ********************************************************;
%Put Begin Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg02_engagement_extract;
%error_check(section=agg02_engagement_extract);

%Put ********************************************************;
%Put Begin VDI/eSync Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg04_vdi_extract;
%error_check(section=agg04_vdi_extract);

%Put ********************************************************;
%Put Begin Referral Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg11_referral_source_extract;;
%error_check(section=agg11_referral_source_extract);

%Put ********************************************************;
%Put Begin Reminder Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
%Put ********************************************************;
%agg18_reminder_mailings;;
%error_check(section=agg18_reminder_mailings);

%mend call_agg_builds;
%call_agg_builds;

/**************************************************************
* Macro to Join the Membership data to the aggs to add the
* criteria data
**************************************************************/
%macro join_membership_add_indexes;

/**************************************************************
* Join each of the Aggs to the Membership data
* These are the fields where criteria may be applied
**************************************************************/
proc sort nodupkey data=sasdata.agg01_membership(keep=period indv_id cust_seg_nbr rel_cd age_id gdr_cd sbscr_empmt_sts_cd
prdct_cd prdct_cd_ky ben_strct_1_cd ben_strct_2_cd franch_1_cd franch_2_cd
subgrp_1_cd subgrp_2_cd fund_arng_cd mkt_seg_cd mbr_zip_cd
elig_mths
fully_insured key_account natl_acct aso uhcfi public_sector acec sh
clinicalprogramgroup channel fundingarrangement
normgroup dm_ind mkt_seg_grp_cd indus_prdct_cd maj_mkt_nbr mkt_cd
data_source_cd hmo_acct_div_nbr co_cd)
out=work.mbrdata(rename=(period=mperiod indv_id=mindv_id));
by period indv_id cust_seg_nbr;
run;

%macro join_agg1(ds);

proc sort data=interim.&ds;
by period indv_id;
run;

proc sql;
create table sasdata.&ds(drop=mperiod mindv_id) as
select a.*,
b.*
from interim.&ds a,
work.mbrdata b
where a.period = b.mperiod
and a.indv_id = b.mindv_id;
quit;

proc sort data=sasdata.&ds;
by cust_seg_nbr period indv_id;
run;

proc datasets library=sasdata;
modify &ds;
index create cust_seg_nbr;
index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
index create prd_indv_key=(period indv_id);
run;
quit;

%mend join_agg1;

%join_agg1(agg16_csp_vdi);
%join_agg1(agg9_1_ods);
%join_agg1(agg04_opportunities);
%join_agg1(agg11_referral_source);
%join_agg1(agg17_hen_member);
%join_agg1(agg17_hen_provider);
%join_agg1(agg18_reminder_mailings);
%join_agg1(agg2_engagement);

/**************************************************************
* Add index to membership
**************************************************************/
%macro index_membership;
proc datasets library=sasdata;
modify agg01_membership;
index create cust_seg_nbr;
index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
index create prd_indv_key=(period indv_id);
run;
quit;
%mend index_membership;
%index_membership;

%mend join_membership_add_indexes;
%join_membership_add_indexes;
%error_check(section=join_membership_add_indexes);

%mend build_aggs;
%build_aggs;

/**************************************************************
* Cleanup step to delete the intermim (staging) area
* Check for a OK Completion code before deleting even if set to YES
**************************************************************/
%macro delete_interim_directory;
%if %upcase(&delete_interim_data) = Y and (&syscc = 0 or &syscc = 4) %then %do;
%put REMOVING INTERIM NOW;
libname interim clear;
%sysexec(rm -rf &interimpath);
%end;
%mend delete_interim_directory;
%delete_interim_directory;

/**************************************************************
* Email completion if requested
**************************************************************/
%macro email_completion;

%if %index(?&emailcomplete?,??) %then;
%else %do;

filename mailout
email
to = ("&emailcomplete")
emailid = ("&emailcomplete")
sender = ("&emailcomplete")
subject = "CESR Agg Build Complete";

data _null_;
file mailout;

put "CESR Production Agg Build Complete" ;
put "Prior Period : &PriorBegDt - &PriorEndDt" ;
put "Current Period : &CurrBegDt - &CurrEndDt" ;
put "Agg Location : &sasdatapath" ;
put "Job Return Code : &syscc" ;

run;

filename mailout clear;

%end;

%mend email_completion;
%email_completion;

/**************************************************************
* Reset the Log Printto
**************************************************************/
proc printto;
run;

Log File:
NOTE: PROCEDURE PRINTTO used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 13k
Page Faults 0
Page Reclaims 18
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

6990 %Put ************************************************************;
************************************************************
11 The SAS System 07:43 Monday, March 8, 2010

6991 %Put ;

6992 %Put syscc syscc4 variable is : &syscc;
syscc syscc4 variable is : 1012
6993 %Put ;

6994 %Put **************************************************************;
**************************************************************
6995 %Put;

6996
6997
6998 /**************************************************************
6999 * Call the Macros to build the Aggs
7000 **************************************************************/
7001 %macro build_aggs;
7002
7003 /*****************************************************************
7004 * Print Current Client's parms in the SAS Log file.
7005 *****************************************************************/
7006 %Put ************************************************************;
7007 %Put ;
7008 %Put Prior Begin Date : &PriorBegDt;
7009 %Put Prior End Date : &PriorEndDt;
7010 %Put Current Begin Date : &CurrBegDt;
7011 %Put Current End Date : &CurrEndDt;
7012 %Put ;
7013 %Put Customer Segs : &CustSegs;
7014 %Put ;
7015 %Put **************************************************************;
7016 %Put;
7017
7018 /**************************************************************
7019 * Macro to pull the membership data for the last month in the
7020 * current reporting period to identify the clients that
7021 * the report should run for
7022 **************************************************************/
7023 %Put ********************************************************;
7024 %Put Begin Account ID Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7025 %Put ********************************************************;
7026 /* %identify_key_accounts;
7027 %error_check(section=Identify_Key_Accounts);
7028 */
7029
7030 %macro call_agg_builds;
7031
7032 %Put ********************************************************;
7033 %Put Begin Membership Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7034 %Put ********************************************************;
7035 %agg01_membership_extract;
7036 %error_check(section=agg01_membership_extract);
7037
7038 %Put ********************************************************;
7039 %Put Begin CSP Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7040 %Put ********************************************************;
7041 %agg16_csp_engagement_extract;
7042 %error_check(section=agg16_csp_engagement_extract);
7043
12 The SAS System 07:43 Monday, March 8, 2010

7044 %Put ********************************************************;
7045 %Put Begin Closure Reason Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7046 %Put ********************************************************;
7047 %agg09_closure_reasons_1_extract;
7048 %error_check(section=agg09_closure_reasons_1_extract);
7049
7050 %Put ********************************************************;
7051 %Put Begin HeltheNotes Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7052 %Put ********************************************************;
7053 %agg17_hen_extract;
7054 %error_check(section=agg17_hen_extract);
7055
7056 %Put ********************************************************;
7057 %Put Begin Engagement Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7058 %Put ********************************************************;
7059 %agg02_engagement_extract;
7060 %error_check(section=agg02_engagement_extract);
7061
7062 %Put ********************************************************;
7063 %Put Begin VDI/eSync Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7064 %Put ********************************************************;
7065 %agg04_vdi_extract;
7066 %error_check(section=agg04_vdi_extract);
7067
7068 %Put ********************************************************;
7069 %Put Begin Referral Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7070 %Put ********************************************************;
7071 %agg11_referral_source_extract;;
7072 %error_check(section=agg11_referral_source_extract);
7073
7074 %Put ********************************************************;
7075 %Put Begin Reminder Source Processing. Date: %sysfunc(today(),mmddyy10.) Time: %sysfunc(time(),time.);
7076 %Put ********************************************************;
7077 %agg18_reminder_mailings;;
7078 %error_check(section=agg18_reminder_mailings);
7079
7080
7081 %mend call_agg_builds;
7082 %call_agg_builds;
7083
7084 /**************************************************************
7085 * Macro to Join the Membership data to the aggs to add the
7086 * criteria data
7087 **************************************************************/
7088 %macro join_membership_add_indexes;
7089
7090 /**************************************************************
7091 * Join each of the Aggs to the Membership data
7092 * These are the fields where criteria may be applied
7093 **************************************************************/
7094 proc sort nodupkey data=sasdata.agg01_membership(keep=period indv_id cust_seg_nbr rel_cd age_id gdr_cd
7094 ! sbscr_empmt_sts_cd
7095 prdct_cd prdct_cd_ky ben_strct_1_cd ben_strct_2_cd
7095 ! franch_1_cd franch_2_cd
7096 subgrp_1_cd subgrp_2_cd fund_arng_cd mkt_seg_cd mbr_zip_cd
7097 elig_mths
7098 fully_insured key_account natl_acct aso uhcfi public_sector
7098 ! acec sh
13 The SAS System 07:43 Monday, March 8, 2010

7099 clinicalprogramgroup channel fundingarrangement
7100 normgroup dm_ind mkt_seg_grp_cd indus_prdct_cd maj_mkt_nbr
7100 ! mkt_cd
7101 data_source_cd hmo_acct_div_nbr co_cd)
7102 out=work.mbrdata(rename=(period=mperiod indv_id=mindv_id));
7103 by period indv_id cust_seg_nbr;
7104 run;
7105
7106 %macro join_agg1(ds);
7107
7108 proc sort data=interim.&ds;
7109 by period indv_id;
7110 run;
7111
7112 proc sql;
7113 create table sasdata.&ds(drop=mperiod mindv_id) as
7114 select a.*,
7115 b.*
7116 from interim.&ds a,
7117 work.mbrdata b
7118 where a.period = b.mperiod
7119 and a.indv_id = b.mindv_id;
7120 quit;
7121
7122 proc sort data=sasdata.&ds;
7123 by cust_seg_nbr period indv_id;
7124 run;
7125
7126 proc datasets library=sasdata;
7127 modify &ds;
7128 index create cust_seg_nbr;
7129 index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
7130 index create prd_indv_key=(period indv_id);
7131 run;
7132 quit;
7133
7134 %mend join_agg1;
7135
7136 %join_agg1(agg16_csp_vdi);
7137 %join_agg1(agg9_1_ods);
7138 %join_agg1(agg04_opportunities);
7139 %join_agg1(agg11_referral_source);
7140 %join_agg1(agg17_hen_member);
7141 %join_agg1(agg17_hen_provider);
7142 %join_agg1(agg18_reminder_mailings);
7143 %join_agg1(agg2_engagement);
7144
7145 /**************************************************************
7146 * Add index to membership
7147 **************************************************************/
7148 %macro index_membership;
7149 proc datasets library=sasdata;
7150 modify agg01_membership;
7151 index create cust_seg_nbr;
7152 index create fund_mkt_key=(fund_arng_cd mkt_seg_cd);
7153 index create prd_indv_key=(period indv_id);
7154 run;
7155 quit;
14 The SAS System 07:43 Monday, March 8, 2010

7156 %mend index_membership;
7157 %index_membership;
7158
7159 %mend join_membership_add_indexes;
7160 %join_membership_add_indexes;
7161 %error_check(section=join_membership_add_indexes);
7162
7163
7164 %mend build_aggs;
7165 %build_aggs;
************************************************************

Prior Begin Date : '07-JUL-2009'
Prior End Date : '31-DEC-2009'
Current Begin Date : '07-JUL-2009'
Current End Date : '31-DEC-2009'

Customer Segs : '000228542','228542'

**************************************************************

********************************************************
Begin Account ID Processing. Date: 03/08/2010 Time: 7:43:12
********************************************************
********************************************************
Begin Membership Processing. Date: 03/08/2010 Time: 7:43:12
********************************************************
************************************************************

syscc variable is : 1012
Prior Begin Date : '07-JUL-2009'
Prior End Date : '31-DEC-2009'
Current Begin Date : '07-JUL-2009'
Current End Date : '31-DEC-2009'

Customer Segs : '000228542','228542'

**************************************************************

NOTE: Deleting INTERIM.MEMBERSHIP_PRIOR (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 1
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting INTERIM.MEMBERSHIP_CURRENT (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
15 The SAS System 07:43 Monday, March 8, 2010

real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 1
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 371k
Page Faults 0
Page Reclaims 20
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

rprd=
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
16 The SAS System 07:43 Monday, March 8, 2010

NOTE: There were 21960 observations read from the data set INTERIM.MEMBER_200908.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 21960 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.11 percent.
Compressed is 216 pages; un-compressed would require 349 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.06 seconds
system cpu time 0.01 seconds
Memory 1041k
Page Faults 13
Page Reclaims 261
Page Swaps 0
Voluntary Context Switches 7
Involuntary Context Switches 10
Block Input Operations 0
Block Output Operations 0

NOTE: There were 21960 observations read from the data set INTERIM.MEMBER_200908.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 21960 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.11 percent.
Compressed is 216 pages; un-compressed would require 349 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.05 seconds
system cpu time 0.01 seconds
Memory 1040k
Page Faults 3
Page Reclaims 194
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 24
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 21960 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
17 The SAS System 07:43 Monday, March 8, 2010

NOTE: There were 21705 observations read from the data set INTERIM.MEMBER_200909.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 43665 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.33 percent.
Compressed is 428 pages; un-compressed would require 694 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.14 seconds
system cpu time 0.02 seconds
Memory 1390k
Page Faults 12
Page Reclaims 520
Page Swaps 0
Voluntary Context Switches 13
Involuntary Context Switches 38
Block Input Operations 0
Block Output Operations 0

NOTE: There were 21960 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 21705 observations read from the data set INTERIM.MEMBER_200909.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 43665 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.33 percent.
Compressed is 428 pages; un-compressed would require 694 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.15 seconds
system cpu time 0.03 seconds
Memory 1390k
Page Faults 3
Page Reclaims 430
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 38
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 43665 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 21660 observations read from the data set INTERIM.MEMBER_200910.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 65325 observations and 29 variables.
18 The SAS System 07:43 Monday, March 8, 2010

NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.44 percent.
Compressed is 639 pages; un-compressed would require 1038 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.21 seconds
system cpu time 0.03 seconds
Memory 1390k
Page Faults 11
Page Reclaims 419
Page Swaps 0
Voluntary Context Switches 17
Involuntary Context Switches 51
Block Input Operations 0
Block Output Operations 0

NOTE: There were 43665 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 21660 observations read from the data set INTERIM.MEMBER_200910.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 65325 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.44 percent.
Compressed is 639 pages; un-compressed would require 1038 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.20 seconds
system cpu time 0.03 seconds
Memory 1390k
Page Faults 3
Page Reclaims 410
Page Swaps 0
Voluntary Context Switches 16
Involuntary Context Switches 56
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 65325 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 21620 observations read from the data set INTERIM.MEMBER_200911.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 86945 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.45 percent.
Compressed is 850 pages; un-compressed would require 1381 pages.
19 The SAS System 07:43 Monday, March 8, 2010

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.24 seconds
system cpu time 0.04 seconds
Memory 1390k
Page Faults 11
Page Reclaims 421
Page Swaps 0
Voluntary Context Switches 17
Involuntary Context Switches 119
Block Input Operations 0
Block Output Operations 0

NOTE: There were 65325 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 21620 observations read from the data set INTERIM.MEMBER_200911.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 86945 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.45 percent.
Compressed is 850 pages; un-compressed would require 1381 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.27 seconds
system cpu time 0.04 seconds
Memory 1390k
Page Faults 3
Page Reclaims 410
Page Swaps 0
Voluntary Context Switches 20
Involuntary Context Switches 76
Block Input Operations 0
Block Output Operations 0

rprd=B
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 413k
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

NOTE: There were 86945 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 21486 observations read from the data set INTERIM.MEMBER_200912.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 108431 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.39 percent.
Compressed is 1061 pages; un-compressed would require 1722 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
20 The SAS System 07:43 Monday, March 8, 2010

user cpu time 0.32 seconds
system cpu time 0.05 seconds
Memory 1390k
Page Faults 9
Page Reclaims 424
Page Swaps 0
Voluntary Context Switches 27
Involuntary Context Switches 92
Block Input Operations 0
Block Output Operations 0

NOTE: There were 86945 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 21486 observations read from the data set INTERIM.MEMBER_200912.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 108431 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.39 percent.
Compressed is 1061 pages; un-compressed would require 1722 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.32 seconds
system cpu time 0.05 seconds
Memory 1390k
Page Faults 5
Page Reclaims 410
Page Swaps 0
Voluntary Context Switches 29
Involuntary Context Switches 80
Block Input Operations 0
Block Output Operations 0

NOTE: Compressing data set WORK.MTHS1 increased size by 41.76 percent.
Compressed is 129 pages; un-compressed would require 91 pages.
NOTE: Table WORK.MTHS1 created, with 22835 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.33 seconds
system cpu time 0.10 seconds
Memory 775k
Page Faults 7
Page Reclaims 186
Page Swaps 0
Voluntary Context Switches 13
Involuntary Context Switches 108
Block Input Operations 0
Block Output Operations 0

NOTE: Compressing data set WORK.MTHS2 increased size by 41.76 percent.
Compressed is 129 pages; un-compressed would require 91 pages.
NOTE: Table WORK.MTHS2 created, with 22835 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.32 seconds
system cpu time 0.10 seconds
21 The SAS System 07:43 Monday, March 8, 2010

Memory 775k
Page Faults 6
Page Reclaims 122
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 163
Block Input Operations 0
Block Output Operations 0

NOTE: There were 108431 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 22835 observations and 28 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 38.64 percent.
Compressed is 216 pages; un-compressed would require 352 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.15 seconds
system cpu time 0.01 seconds
Memory 1040k
Page Faults 3
Page Reclaims 176
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 51
Block Input Operations 0
Block Output Operations 0

NOTE: There were 108431 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 22835 observations and 28 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 38.64 percent.
Compressed is 216 pages; un-compressed would require 352 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.18 seconds
system cpu time 0.02 seconds
Memory 1040k
Page Faults 3
Page Reclaims 176
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 42
Block Input Operations 0
Block Output Operations 0

************************************************************

syscc 2 variable is : 1012

**************************************************************

NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 22835 observations read from the data set WORK.MTHS1.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 22835 observations and 29 variables.
22 The SAS System 07:43 Monday, March 8, 2010

NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 39.12 percent.
Compressed is 221 pages; un-compressed would require 363 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.08 seconds
system cpu time 0.01 seconds
Memory 1734k
Page Faults 3
Page Reclaims 502
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 23
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.MTHS1 (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0

NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 22835 observations read from the data set WORK.MTHS2.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 22835 observations and 29 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 39.12 percent.
Compressed is 221 pages; un-compressed would require 363 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.08 seconds
system cpu time 0.01 seconds
Memory 1734k
Page Faults 3
Page Reclaims 500
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 23
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.MTHS2 (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
23 The SAS System 07:43 Monday, March 8, 2010

Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 1438667 observations read from the data set OTOOL.CLIENT_FLATFILE_WITH_RULES.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.CLIENT_DATA has 1438667 observations and 9 variables.
NOTE: Compressing data set WORK.CLIENT_DATA decreased size by 7.84 percent.
Compressed is 13128 pages; un-compressed would require 14245 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 21.00 seconds
user cpu time 1.80 seconds
system cpu time 2.59 seconds
Memory 83051k
Page Faults 3457
Page Reclaims 78457
Page Swaps 0
Voluntary Context Switches 9747
Involuntary Context Switches 1979
Block Input Operations 0
Block Output Operations 0

NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 1438667 observations read from the data set WORK.CLIENT_DATA.
NOTE: The data set INTERIM.MEMBERSHIP_PRIOR has 22835 observations and 40 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_PRIOR decreased size by 41.30 percent.
Compressed is 280 pages; un-compressed would require 477 pages.
NOTE: DATA statement used (Total process time):
real time 2.00 seconds
user cpu time 1.23 seconds
system cpu time 0.07 seconds
Memory 1634k
Page Faults 74
Page Reclaims 566
Page Swaps 0
Voluntary Context Switches 29
Involuntary Context Switches 152
Block Input Operations 0
Block Output Operations 0

************************************************************

syscc variable is : 1012
Prior Begin Date : '07-JUL-2009'
Prior End Date : '31-DEC-2009'
Current Begin Date : '07-JUL-2009'
Current End Date : '31-DEC-2009'

24 The SAS System 07:43 Monday, March 8, 2010

Customer Segs : '000228542','228542'

**************************************************************

NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: There were 1438667 observations read from the data set WORK.CLIENT_DATA.
NOTE: The data set INTERIM.MEMBERSHIP_CURRENT has 22835 observations and 42 variables.
NOTE: Compressing data set INTERIM.MEMBERSHIP_CURRENT decreased size by 41.45 percent.
Compressed is 291 pages; un-compressed would require 497 pages.
NOTE: DATA statement used (Total process time):
real time 2.00 seconds
user cpu time 1.16 seconds
system cpu time 0.07 seconds
Memory 1601k
Page Faults 11
Page Reclaims 207
Page Swaps 0
Voluntary Context Switches 27
Involuntary Context Switches 191
Block Input Operations 0
Block Output Operations 0

NOTE: Deleting WORK.CLIENT_DATA (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.03 seconds
Memory 15k
Page Faults 0
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_PRIOR.
NOTE: There were 22835 observations read from the data set INTERIM.MEMBERSHIP_CURRENT.
NOTE: The data set SASDATA.AGG01_MEMBERSHIP has 22835 observations and 43 variables.
NOTE: Compressing data set SASDATA.AGG01_MEMBERSHIP decreased size by 42.72 percent.
Compressed is 291 pages; un-compressed would require 508 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.29 seconds
system cpu time 0.01 seconds
Memory 1782k
Page Faults 13
Page Reclaims 372
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 57
Block Input Operations 0
Block Output Operations 0
25 The SAS System 07:43 Monday, March 8, 2010

mcc=1012
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 357k
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: The file MAILOUT is:
E-Mail Access Device

Message sent
To: "dan.donahue@optumhealth.com"
Cc:
Bcc:
Subject: CESR Aggregate Build Error In agg01_membership_extract - Action Required
Attachments:
NOTE: 2 records were written to the file MAILOUT.
The minimum record length was 54.
The maximum record length was 54.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 388k
Page Faults 1
Page Reclaims 72
Page Swaps 0
Voluntary Context Switches 7
Involuntary Context Switches 10
Block Input Operations 0
Block Output Operations 0

NOTE: Fileref MAILOUT has been deassigned.
ERROR: Execution terminated by an ABORT statement at line 8022 column 192, it specified the ABEND option.
_ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 329k
Page Faults 1
Page Reclaims 10
Page Swaps 0
Voluntary Context Switches 0
26 The SAS System 07:43 Monday, March 8, 2010

Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0