%macro check_errors;
/*delete any stray errors data set prior to doing the lookup*/
proc datasets library=work; delete errors; quit;
proc sql;
create table errors as
select distinct (d1.physician),
case
when d1.physician ne d2.phy_name then 'YES' else 'NO'
end as is_it_newname
from whole d1 left outer join caremarkphysicians d2 on d1.physician=d2.Phy_Name
where d1.physician ne '';
quit;
/*Call the DATA step EXIST function with %SYSFUNC to determine if data set exists.*/
%let err = %sysfunc(exist(work.errors));
%if err = 0 %then %do;
/* INSERT CODE HERE TO RUN IF ERRORS DATA SET DOES NOT EXIST */
proc sql;
create table whole_v1 as
select d1.*,
case
when d2.nEWPHY_NAME eq '' then d1.physician
else d2.nEWPHY_NAME
end as physician_name
from whole d1 left outer join caremarkphysicians d2 on d1.physician=d2.Phy_Name;
quit;
proc sql;
create table whole_v2 as
select a.*,b.terr_id,b.bsm,b.region,b.city
from whole_v1(drop=md_city) a left outer join caremarkziptoterr b on a.md_zip=b.zip;
quit;
/*****
Report1:Prescriber Report Summary
******/
options missing='';
ods html file="T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\report1- %sysfunc(DATE(),mmddYYd10.).xls";
proc report data=whole_v2 nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id bsm physician_name total_scripts, year, productgroup;
define terr_id/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE year / across ''
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
DEFINE total_scripts / sum format=comma12. ' '
STYLE (COLUMN) = { font_size=1.5};
/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terr_id;
if terr_id ne '' then hold=terr_id;
if terr_id eq '' then terr_id=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
break after terr_id/summarize skip ol style=[font_weight=bold background=grey];
run;
ods html close;
/***************** End of report1*************/
/*****
Report2:Caremark Prescriber Report
******/
proc sql noprint;
create table rpt2a as
select distinct terr_id,region,bsm, md_zip,md_addr1,md_addr2,city,md_state,physician_name, sum(total_scripts) as T_yr, productgroup
from whole_v2
group by terr_id,region, md_zip, bsm, physician_name, productgroup,md_zip,city,md_state;
quit;
ods html file="T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\rpt2a-%sysfunc(DATE(),mmddYYd10.).xls";
proc report data=rpt2a nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id region bsm md_zip physician_name md_addr1 md_addr2 city md_state t_yr, productgroup;
define terr_id/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define region/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_zip/group '5 digit zip'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
/* DEFINE year / across*/
/* STYLE (header) = {just=left font_size=1};*/
DEFINE year_month / across
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
DEFINE t_yr / format=comma9. ''
STYLE (column) = {font_size=1};
DEFINE t_yr_m/ format=comma9. ''
STYLE (column) = {font_size=1};
define md_addr1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_addr2/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define city/group 'md_city'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_state/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terr_id;
if terr_id ne '' then hold=terr_id;
if terr_id eq '' then terr_id=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
compute region;
if region ne '' then hold3=region;
if region eq '' then region=hold3;
endcomp;
break after terr_id/summarize skip ol style=[font_weight=bold background=grey];
run;
ods html close;
proc sql noprint;
create table rpt2b as
select distinct terr_id,region,bsm, md_zip,md_addr1,md_addr2,city,md_state,physician_name, sum(total_scripts) as T_yr_m, productgroup,year_month
from whole_v2
group by terr_id,region, md_zip, bsm, physician_name, year_month, productgroup,md_zip,city,md_state,year_month;
quit;
ods html file="T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\rpt2b-%sysfunc(DATE(),mmddYYd10.).xls";
proc report data=rpt2b nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id region bsm md_zip physician_name md_addr1 md_addr2 city md_state t_yr_m,year_month, productgroup;
define terr_id/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define region/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_zip/group '5 digit zip'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE year_month / across
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
DEFINE t_yr_m/ format=comma9. ''
STYLE (column) = {font_size=1};
define md_addr1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_addr2/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define city/group 'md_city'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_state/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terr_id;
if terr_id ne '' then hold=terr_id;
if terr_id eq '' then terr_id=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
compute region;
if region ne '' then hold3=region;
if region eq '' then region=hold3;
endcomp;
break after terr_id/summarize skip ol style=[font_weight=bold background=grey];
run;
ods html close;
/***************** End of report2*************/
/********************
Report3:Caremark Report by Patient (parta)
*********************/
proc sql noprint;
create table pat_seq_id_yr as
select distinct terr_id,region,bsm, md_zip,md_addr1,pat_seq_id1,md_addr2,city,md_state,physician_name, sum(total_scripts) as T_yr, year, productgroup
from whole_v2
group by terr_id,region, md_zip, bsm, physician_name ,pat_seq_id1, year, productgroup,md_zip,city,md_state;
quit;
ods html file="T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\rpt3a-%sysfunc(DATE(),mmddYYd10.).xls";
proc report data=pat_seq_id_yr nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id region bsm md_zip physician_name md_addr1 md_addr2 city md_state pat_seq_id1 t_yr,year, productgroup ;
define terr_id/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define region/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_zip/group '5 digit zip'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE pat_seq_id1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {font_size=1.5};
DEFINE year / across
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
DEFINE t_yr / sum format=comma9. ''
STYLE (column) = {font_size=1};
define md_addr1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_addr2/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define city/group 'md_city'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_state/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terr_id;
if terr_id ne '' then hold=terr_id;
if terr_id eq '' then terr_id=hold;
endcomp;
compute bsm;
if bsm ne '' then hold1=bsm;
if bsm eq '' then bsm=hold1;
endcomp;
compute region;
if region ne '' then hold3=region;
if region eq '' then region=hold3;
endcomp;
break after terr_id/summarize skip ol style=[font_weight=bold background=grey];
run;
ods html close;
/******End report3:Caremark Report by Patient (parta)********************/
/********************
Report3:Caremark Report by Patient (partb)
*********************/
proc sql noprint;
create table pat_seq_id_yr_m as
select distinct terr_id,region,bsm, md_zip,md_addr1,pat_seq_id1,md_addr2,city,md_state,physician_name, sum(total_scripts) as T_yr_m, year_month, productgroup
from whole_v2
group by terr_id,region, md_zip, bsm, physician_name ,pat_seq_id1, year_month, productgroup,md_zip,city,md_state;;
quit;
ods html file="T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\rpt3b-%sysfunc(DATE(),mmddYYd10.).xls";
proc report data=pat_seq_id_yr_m nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id region bsm md_zip physician_name md_addr1 md_addr2 city md_state pat_seq_id1 t_yr_m,year_month, productgroup;
define terr_id/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define region/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define bsm/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_zip/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5};
DEFINE physician_name / group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
DEFINE pat_seq_id1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {font_size=1.5};
DEFINE year_month / across
STYLE (header) = {just=left font_size=1};
DEFINE productgroup / across
STYLE (header) = [FOREGROUND = black font_size=1];
DEFINE t_yr_m / sum format=comma9. ''
STYLE (column) = {font_size=1};
define md_addr1/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_addr2/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define city/group 'md_city'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
define md_state/group
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {just=left font_size=1.5};
/****
reason to create compute blocks:
repeat GROUP or ORDER variables for every row.
***/
compute terr_id;
if terr_id ne '' then hold=terr_id;
if terr_id eq '' then terr_id=hold;
endcomp;
compute bsm;
if bsm ne '' then hold2=bsm;
if bsm eq '' then bsm=hold2;
endcomp;
compute region;
if region ne '' then hold3=region;
if region eq '' then region=hold3;
endcomp;
compute md_zip;
if md_zip ne '' then hold4=md_zip;
if md_zip eq '' then md_zip=hold4;
endcomp;
break after terr_id/summarize skip ol style=[font_weight=bold background=grey ];
run;
ods html close;
/******Endreport3:Caremark Report by Patient (partb)********************/
%end;
%else %do;
%put Errors occurred. Exporting Errors data set to erordatabase;
proc export data=work.errors
dbms=excel2000
outfile='T:\NovoLand\Biopharm Business Analysis SAS Project (2009)\BSM Prescriber Reports\errors.xls';
run;
%end;
%mend;
%check_errors;