> 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 = ¤ddt.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 : ¤ddt";
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 §ion - Action Required";
data _null_;
file mailout;
put "CESR Aggregate Build Error In §ion";
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