SAS9 Format and Macro Examples_Sanitized.sas

/*	Project Title:				IHA Target File 3 2023
	Requesting Department:		Internal
	Requestor:					XXXXXXXXXX
	Origination Date:			12/6/2023
	Requested Completion Date:	12/11/2023
	Date received:				12/6/2023
	Assigned Priority:			High
	Assigned Delivery Date:		12/11/2023
	RDA_Project_number:			XXXXXXXXXX
	Assigned Analyst:			Meagan Windler	
	Assigned Days: 				3
	Support Analyst: 	
	Perc_Support:				0
	Assigned Completion Date:	12/11/2023
	Remarks: 	
	Use member list from Warren Clinic.  Confirm members are still active (remove any that are not).  
	Create Emcara chase list and gaps file.  

*/

/**************************************************/
	/*Define Macro Variables*/
/**************************************************/

*month we are running for;
%Let DataMonth=202310; /* (Format:yyyymm) */

/* Clearing Work Library */
proc datasets nolist nodetails lib=Work kill; quit;

/* Setting Valid Variable Names to Change Blanks to Underscore when Importing */
options validvarname=V7;

/* Searching for custom formats */
options fmtsearch=(work formats) fmterr;


/**************************************************/
	/*User Variables*/
/**************************************************/

/* Measurement Year (Current Calendar Year)*/
%Let MeasurementYear=2023;

/* Member List */
*Using a member list generated by Warren Clinic;
%Let MemberFile=November 2023 Community Care Patients not seen;

/* Change Extract */
*This is going to be the end of the previous month;
%Let LimitationPartC="30NOV2023"d;

/* Part D Date - Check that tables in location are updated, should be in the
last couple of weeks*/
*check the "final_rates_diab table located here: 
\\XXXXXXXXXX\Production\W_Stars Reporting - Medication Adherence\Data
for the most up to date linked end date, that will be the value you use here;
%Let LimitationPartD="04DEC2023"d;

/* Cut Point File for Measure Names */
*This is an excel sheet at location \\XXXXXXXXXX\HDA\Production\M_Medicare HEDIS Rates\Source Data
and it should be the most up to date version. Looks like it changes once a year;
%Let CutPointFile=STARS 2024 Cut Points;


/**************************************************/
	/*Setup*/
/**************************************************/

/* Setting standard date variables for today */
%Let rundate=%sysfunc(today(),mmddyy10.); 						/* Format: mm/dd/yyyy */
%Let logdate=%sysfunc(today(),yymmddn8.); 						/* Format: yyyymmdd */
%Let analysisdate=%sysfunc(inputn(&rundate,mmddyy10.),date9.); 	/* Format: ddmmmyyyy */

/* Defining User and Display Macro Variables */
%Let analyst=MW;
%Let rda=XXXXXXXXXX;

/* Server Address */
%Let server=XXXXXXXXXX;

/* Dynamically establishing path based on location of sas file being run */
data _null_;
	/* If you open the program via the Server */
	if substr(compress(&_SASPROGRAMFILE,"'"),1,1)='N' then 
		call symput("File_Path",cat("&server\DAR\",substr(compress(&_SASPROGRAMFILE,"'"),4)));
	/* If you open the program via the File Explorer */
	if substr(compress(&_SASPROGRAMFILE,"'"),1,1)='G' then 
		call symput("File_Path",cat("&server\",substr(compress(&_SASPROGRAMFILE,"'"),4)));
	/* If the SAS Program file macro is correct... */
	if substr(compress(&_SASPROGRAMFILE,"'"),1,11)='\\twr-sas-1' then 
		call symput("File_Path",&_SASPROGRAMFILE);
run;
%Let File_Name=%scan(&File_Path,-1,'\');
%Let Code_Folder=%scan(&File_Path,-2,'\');
%Let Code_Path=%substr(%Sysfunc(Tranwrd(&File_Path,&File_Name,)),1,%length(%Sysfunc(Tranwrd(&File_Path,&File_Name,)))-1);
%Let Path=%substr(%Sysfunc(Tranwrd(&Code_Path,&Code_Folder,)),1,%length(%Sysfunc(Tranwrd(&Code_Path,&Code_Folder,)))-1);
%Put &Path;

/* Locations */
%let ExtractLocation=XXXXXXXXXX\DAR\HDA\Regulatory Reporting\HEDIS\Compliance Extracts\Standard;
%Let change_sasdata=XXXXXXXXXX\DAR\HDA\Regulatory Reporting\HEDIS\HEDIS MY&MeasurementYear\Change Healthcare;

/* Libraries */
libname npi "XXXXXXXXXX\Regulatory Reporting\NPI DATA\SAS DATA";
libname partd "&server\XXXXXXXXXX\W_Stars Reporting - Medication Adherence\Data"; /* Part D Data, Managed by Michael*/
libname hedis "&change_sasdata\SAS DATA"; /* Member Crosswalk & Claims */
libname extract "\\XXXXXXXXXXRegulatory Reporting\HEDIS\Compliance Extracts\SAS Tables"; *Hedics claims Extract;

/* Loading format for Change Crosswalk */
proc format cntlin=hedis.xwalk; run;

/**************************************************/
	/*Loading Members*/
/**************************************************/

/* Loading members to report on */
proc import datafile="&Path\Data\&MemberFile..xlsx"
	dbms=excel
	out=PopHealth_Members
	replace;
	sheet="CCOK Patients";
run;

/* Applying Crosswalk */
data PopHealth_Members;
	set PopHealth_Members;
	MemberAddno=substr(Member_ID,1,9);
	MemberXwalk=put(Member_ID,$xwalk.);
run;


/**************************************************/
	/*Medicare Members*/
/**************************************************/

/* Looking for Medicare Members active during the year */
data member_spans;
	set memb.membspan;
	where input(ymdeff,? yymmdd8.)<=today()
		  and input(ymdend,? yymmdd8.)>="01Jan&MeasurementYear"d
	      and void=" "
		  and prog_ in ("XXXXXXXXXX","XXXXXXXXXX","XXXXXXXXXX");
	Member9=substr(Member_,1,9);
	Original_Member=Member_;
	Member_=put(Member_,$xwalk.);
	Prov_Number=scan(aff_,1,"");
	/* Removing 1 Day Spans (Usually establsihing Medicare Member) */
	date_start=input(ymdeff,?? yymmdd8.);
	date_end=input(ymdend,?? yymmdd8.);
	if date_start~=date_end;
	format date_: mmddyy10.;
run;

/* Sorting to find most recent span */
proc sort data=member_spans; by Member_ date_end; run;

/* Finding latest span */
data member_spans_recent;
	set member_spans;
	by Member_ date_end;
	if last.Member_=1;
	/* Need date to use AttachAff Info Macro */
	Today=today();
	/* Flagging Active Records */
	if date_start<=today<=date_end then Flag_Active=1;
	strtdate=min(date_end,Today);
	format today strtdate mmddyy10.;
run;

/* Flagging All Active Members */
%Flagem(member_spans_recent,Original_Member,$All_OriginalMembers);
%Flagem(member_spans_recent,member_,$All_Members);
%Flagem(member_spans_recent,Member9,$Find_Members_Addno);

/* Attaching IRS Info */
%AttachAffInfo(member_spans_recent,Spec1 IRS_);

/* Creating formats using Formatme stored macro */
%Formatme(work,member_spans_recent_withinfo,Member_,Prog_,$Member_to_Program);
%Formatme(work,member_spans_recent_withinfo,Member_,Aff_,$Member_to_Affiliation);
%Formatme(work,member_spans_recent_withinfo,Member_,Prov_Number,$Member_to_PCP);
%Formatme(work,member_spans_recent_withinfo,Member_,Spec1,$Member_to_Specialty);
%Formatme(work,member_spans_recent_withinfo,Member_,IRS_,$Member_to_IRS);
%Formatme(work,member_spans_recent_withinfo,Member_,Date_End,$Member_to_Term);
%Formatme(work,member_spans_recent_withinfo,Member_,Flag_Active,$ActiveMembers);

/* Flagging Provider Number for NPI Search */
%Flagem(member_spans_recent_withinfo,Prov_Number,$Find_Provider);


/**************************************************/
	/* Member DOB & MBI */
/**************************************************/

/* Finding DOB, Name & Gender, & MBI */
data membership_details;
	set memb.memb;
	where put(Member_,$All_OriginalMembers.)="*";
	MemberID=put(member_,$xwalk.);
	DOB=input(ymdbirth,yymmdd8.);
	format DOB mmddyy10.;
run;

/* Creating formats */
%Formatme(work,membership_details,Member_,Name_First,$Member_FirstName);
%Formatme(work,membership_details,Member_,Name_Last,$Member_LastName);
%Formatme(work,membership_details,Member_,DOB,$Member_DOB);
%Formatme(work,membership_details,Member_,Sex,$Member_Sex);
%Formatme(work,membership_details,Member_,Alt_Key,$MBI);

/**************************************************/
	/* Launguage */
/**************************************************/


/* Going to initially use the memb table for language (but this is not populated
   very well so will use the data incorporated by HEDIS as well) */
data hedis_langauge;
	set hedis.membership;
	where put(Mem_Unique_ID,$All_Members.)="*";

	/* Key for what each value represents */



/* Update mapping of language codes 01312023 */

	if Spoken_Language="E" then ISOLanguage="EN"; /* English */
	else if Spoken_Language="S" then ISOLanguage="ES"; /* Spanish */
	else if Spoken_Language="C" then ISOLanguage="ZH"; /* Chinese */
	else if Spoken_Language="N" then ISOLanguage="EN"; /* English */
	else if Spoken_Language="D" then ISOLanguage="EN"; /* English */
	else if Spoken_Language="U" then ISOLanguage="EN"; /* English */
	if ISOLanguage~="" then ISOLanguage="EN";
	
	keep Mem_Unique_ID Spoken_Language ISOLanguage;
run;

/* Creating format for language */
%Formatme(work,hedis_langauge,Mem_Unique_ID,ISOLanguage,$Spoken_Language);

/* Creating final table */
data membership_language;
	set memb.memb;
	where put(Member_,$All_OriginalMembers.)="*";
	MemberID=put(member_,$xwalk.);
	/* Need to convert to ISO Codes */
	if language~="" then do;
		if language="SP" then language="ES";
	end;
	/* Otherwise use HEDIS */
	if language="" then do;
		language=put(MemberID,$Spoken_Language.);
	end;
run;

/* Observing Frequencies */
proc freq data=membership_language noprint;
	tables language/out=check_language;
run;

/* Creating format for language */
%Formatme(work,membership_language,Member_,language,$Language);

/**************************************************/
	/* Finding PCP Address */
/**************************************************/

/* Finding address and phone number for each PCP */
data pcp_address;
	set memb.address;
	where put(addno,$Find_Provider.)="*"
    	  and input(ymdend,? yymmdd8.)>today()
		  /* There may be an address listed for the future so I need to 
			 add a restriction on effective date aswell */
		  and input(ymdeff,? yymmdd8.)<=today()
		  and whotype="P"
		  /* Just need Default Address for Provider */
		  and AddType in: ("D")
		  and void=" ";
run;

/* Sorting to find most recent address */
proc sort data=pcp_address; BY addno addtype YmdEnd YmdEff; run;

/* Finding last known address */
data pcp_address_last (keep=addno Address address1 address2 city state zip
							final_myphone email phone3);
	length final_myphone $12.;
    set pcp_address;
    by addno;
    if last.addno;
	Address=catx(" ",address1, address2, city, state, zip);
	/* Looking for meaningful phone number */
    if phone1 not in ("","0000000000") then myphone=phone1;
    else if phone2 not in ("","0000000000") then myphone=phone2;
    else if phone3 not in ("","0000000000") then myphone=phone3;
    else myphone='N/A';
    temp_myphone=compress(myphone,'() -.');
	/* Putting Phone Number in the desired Format */
	if myphone='N/A' then final_myphone=" ";
	else final_myphone=compress(catx("",substr(temp_myphone,1,3),
										substr(temp_myphone,4,3),
										substr(temp_myphone,7,4)));
run;

/* Creating format for address and phone number */
%formatme(work,pcp_address_last,addno,address1,$PCP_to_Address_One);
%formatme(work,pcp_address_last,addno,address2,$PCP_to_Address_Two);
%formatme(work,pcp_address_last,addno,city,$PCP_to_City);
%formatme(work,pcp_address_last,addno,state,$PCP_to_State);
%formatme(work,pcp_address_last,addno,zip,$PCP_to_Zip);
%formatme(work,pcp_address_last,addno,final_myphone,$PCP_to_Phone);

/* Looking at the structure of the provider addresses it looks to be 
   that Phone 3 always contains the Fax Number indicated by the info
   being "FX" */

/* Format for Provider Fax */
%formatme(work,pcp_address_last,addno,phone3,$Provider_Fax);

/**************************************************/
	/* Finding NPI for PCP */
/**************************************************/

/* Pulling all provider records for NPI */
data provider_npi;
	set provider.provider;
	where put(prov_,$Find_Provider.)="*";
run;

/* Creating format for NPI & Credential (if missing from NPI file) */
%Formatme(work,provider_npi,Prov_,NPI,$Provider_NPI);
%Formatme(work,provider_npi,Prov_,Title,$Provider_Credential_Backup);

/* Flagging NPI's for further information search */
%Flagem(provider_npi,NPI,$Find_NPI);

/* Creating format for first and last name */
%Formatme(work,provider_npi,Prov_,FirstName,$Provider_FirstName);
%Formatme(work,provider_npi,Prov_,LastName,$Provider_LastName);


/**************************************************/
	/* Finding Taxonomy for PCP */
/**************************************************/

/* Searching for desired NPI records */
data npi_data;
	set npi.npi_data;
	where put(NPI,$Find_NPI.)="*";
	/* Providers can have taxonomy codes for any state. I need the code
	   that is relevant to Oklahoma which may not be the first one listed */
	if Prvdr_LicenseNbr_State_1="OK" then First_Taxonomy_Code=Prvdr_Taxonomy_Code_1;
	else if Prvdr_LicenseNbr_State_2="OK" then First_Taxonomy_Code=Prvdr_Taxonomy_Code_2;
	else if Prvdr_LicenseNbr_State_3="OK" then First_Taxonomy_Code=Prvdr_Taxonomy_Code_3;
	else if Prvdr_LicenseNbr_State_4="OK" then First_Taxonomy_Code=Prvdr_Taxonomy_Code_4;
	else if Prvdr_LicenseNbr_State_5="OK" then First_Taxonomy_Code=Prvdr_Taxonomy_Code_5;
	/* There were a few providers that did not have a taxonomy code for OK so I
	   am going to go with the first Code listed */
	else First_Taxonomy_Code=Prvdr_Taxonomy_Code_1;
run;

/* Creating formats for Credential & Taxonomy */
%Formatme(work,npi_data,NPI,First_Taxonomy_Code,$Provider_TaxonomyCode);
%Formatme(work,npi_data,NPI,Prvdr_Credential,$Provider_Credential);


/**************************************************/
	/*  Member Address */
/**************************************************/

/* Finding address and phone number for each member */
data member_address;
	set memb.address;
	where put(addno,$Find_Members_Addno.)="*"
		  and input(ymdend,yymmdd8.)>=today()
		  and input(ymdeff,yymmdd8.)<=today()
		  and whotype="S"
		  /* Medicare Members want Mail sent to M, but need D if no M */
		  and AddType in: ("D","M")
		  and void=" ";
run;

/* Sorting to find most recent address */
proc sort data=member_address; BY addno addtype YmdEff YmdEnd; run;

/* Finding last known address */
data member_address_last (keep=addno addtype address: city state zip);
	length Address $100.;
    set member_address;
    by addno;
    if last.addno;
    address=catx(" ",address1, address2);
	/* For whatever reason there was a city record ending with a 
	   comma that misaligns the output, correcting here */
	if city="TULSA," then city="TULSA";
run;

/* Creating formats */
%Formatme(work,member_address_last,Addno,address,$Member_to_Address);
%Formatme(work,member_address_last,Addno,City,$Member_to_City);
%Formatme(work,member_address_last,Addno,State,$Member_to_State);
%Formatme(work,member_address_last,Addno,ZIP,$Member_to_ZIP);

/**************************************************/
	/* Member Phone Numbers  */
/**************************************************/

/* Need to grab primary member phone - limiting to default address records */
data member_phone;
	length final_myphone $120.;
	set memb.address;
	where put(Addno,$Find_Members_Addno.)="*"
		  and input(ymdeff,? yymmdd8.)<=today()<=input(ymdend,? yymmdd8.)
		  and whotype="S"
		  and AddType in: ("D")
		  and void="";

	/* Looking for meaningful phone number and putting into
	   meaningful format */
    if phone1 not in ("","0000000000") then do;
		Priority=1;
		temp_myphone=compress(phone1,'() -.');
		final_myphone=compress(catx("",substr(temp_myphone,1,3),
										substr(temp_myphone,4,3),
										substr(temp_myphone,7,4)));
		temp_storedphone1=final_myphone;
		output member_phone;
	end;
    if phone2 not in ("","0000000000") then do;
		Priority=2;
		temp_myphone=compress(phone2,'() -.');
		final_myphone=compress(catx("",substr(temp_myphone,1,3),
										substr(temp_myphone,4,3),
										substr(temp_myphone,7,4)));
		temp_storedphone2=final_myphone;
		if temp_storedphone2~=temp_storedphone1 then output member_phone;
	end;
    if phone3 not in ("","0000000000") then do;
		Priority=3;
		temp_myphone=compress(phone3,'() -.');
		final_myphone=compress(catx("",substr(temp_myphone,1,3),
										substr(temp_myphone,4,3),
										substr(temp_myphone,7,4)));
		temp_storedphone3=final_myphone;
		if (temp_storedphone3~=temp_storedphone2
			and temp_storedphone3~=temp_storedphone1) then output member_phone;
	end;
run;

/* Need Primary Phone so prioritizing Default over Mailing */
proc sort data=member_phone nodupkey; by Addno AddType Priority final_myphone; run;

/* Keeping default phone1 where available */
data member_phone_primary member_phone_remaining;
	set member_phone;
	by Addno AddType Priority;
	if first.Addno then output member_phone_primary;
	else output member_phone_remaining;
run;

/* Creating format for phone */
%Formatme(work,member_phone_primary,Addno,final_myphone,$MemberPrimaryPhone);

/* Need Secondary Phone so re-sorting */
proc sort data=member_phone_remaining; by Addno AddType Priority; run;

/* Keeping default phone2 or 3 where available */
data member_phone_secondary;
	set member_phone_remaining;
	by Addno AddType Priority;
	if first.Addno;
run;

/* Creating format for phone */
%Formatme(work,member_phone_secondary,Addno,final_myphone,$MemberSecondaryPhone);


/**************************************************/
	/* Formats for Star Measures  */
/**************************************************/

/* This table will need to updated each year as new measures are included or existing
   ones are removed */

/* Importing CMS Star Table for formats */
proc import datafile="\\twr-sas-1\DAR\HDA\Production\M_Medicare HEDIS Rates\Source Data\&CutPointFile..xlsx"
	dbms=excel
	out=CMS_Stars
	replace;
run;

/* Creating additional key for filtering */
data CMS_Stars;
	set CMS_Stars;
	FilterKey=catx("|",ChangeHealthcare_Measure,ChangeHealthcare_SubMeasure);
run;

/* Flagging SubMeasures to Keep */
%Flagem(CMS_Stars,ChangeHealthcare_Measure,$CMS_Measures);
%Flagem(CMS_Stars,ChangeHealthcare_SubMeasure,$CMS_SubMeasures);
%Flagem(CMS_Stars,FilterKey,$CMS_MeasureSubmeasureKey);

/* Creating format for Measure Abreviations */
%Formatme(work,CMS_Stars,ChangeHealthcare_SubMeasure,Measure_Abreviation,$CMS_ChangeSubMeasure);
%Formatme(work,CMS_Stars,Measure_Abreviation,MyMeasure,$CMS_Measure_Title);
%Formatme(work,CMS_Stars,MyMeasure,Measure_Abreviation,$CMS_Measure_Abreviation);


/**************************************************/
	/* HEDIS Extract   */
/**************************************************/

/* Loading Extract that has already been imported and saved */
data ChangeHealthcare_Extract_Raw;
	set extract.HEDIS_Extract_&DataMonth;
run;
 
/* Checking format of variables that commonly cause issues */
proc contents data=ChangeHealthcare_Extract_Raw 
			  out=Check_Extract_Variables(keep=Name Type Length) noprint;
run;

/* Step for any filters if needed */
data ChangeHealthcare_Extract;
	set ChangeHealthcare_Extract_Raw;

/*	 Applying Crosswalk */
	MemberXwalk=put(MemberID,$xwalk.);

	/* Renaming ECDS SubMeasures because they are indentical to the standard
	   measures - Prevents format errors */
	if substr(Measure,length(Measure)-1,2)="-E" then SubMeasure=catx("",SubMeasure,"(ECDS)");

	/* For measures with multiple sub-measures I need to filter down to the
	   relevant indicator(s) & ensure the measure is named correctly */
	if put(catx("|",Measure,SubMeasure),$CMS_MeasureSubmeasureKey.)="*";
	Final_Measure_Abreviation=put(SubMeasure,$CMS_ChangeSubMeasure.);
	Final_Measure=put(Final_Measure_Abreviation,$CMS_Measure_Title.);

	/* Limiting OMW Fractures to those that can be intervened with */
	if Final_Measure_Abreviation="OMW" then do;
		if AcceptableDateRangeStart<=today()<=AcceptableDateRangeEnd;
	end;
	/* Limiting to relevant measures */
	else do;
/*		 Either not star related or highly time sensitive */
		if Final_Measure_Abreviation not in: ("","PCR","TRC","FMC","CDC Neph");
	end;
%Runquit;

/* If there are any errors I need this to stop so that they can be
   corrected before moving forwards */

/* Inversing CDC Blood Sugar Controlled (Hit defined to be when Poor Control is not a hit) */
data Final_Extract_Table Final_Extract_Table_Exclusions
	 /*Final_Extract_Table_ECDS*/;
	set ChangeHealthcare_Extract;
	if Final_Measure_Abreviation="CDC A1c" then do;
		if Admin_Numerator=1 then Inverse_Admin_Numerator=0;
		else if Admin_Numerator=0 then Inverse_Admin_Numerator=1;
		Final_Admin_Numerator=Inverse_Admin_Numerator;
	end;
	else Final_Admin_Numerator=Admin_Numerator;
	/* Change is providing records for exclusions, we need to remove these to
	   prevent reporting gaps that don't count */
	if Admin_Denominator=0 then output Final_Extract_Table_Exclusions;
/*	else if substr(Measure,length(Measure)-1,2)="-E" then output Final_Extract_Table;*/
	else output Final_Extract_Table;
	drop Admin_Numerator;
run;

/* Noticing some duplicate records for BP that I am going to handle
- Not ideal but have no other option (records look identical except for
comments listing last BP differently) */
proc sort data=Final_Extract_Table nodupkey dupout=ExtractDuplicates; by Lob MemberID Measure SubMeasure EligibilityEventDate; run;


/**************************************************/
	/* Part C Compliance  */
/**************************************************/


/* Since I need to use the Member ID from the initial list so that files are consistent
   I have to go backwards from the crosswalk ID. Since multiple numbers can be mapped
   together I am opting for the safe route of building a format and tacking on compliance
   data to the original list instead of trying to map member ID onto the compliance data
   directly */

/* Creating a Key for Transpose */
data PartC_Compliance;
	set Final_Extract_Table;
	TransposeKey=catx(":",Final_Measure_Abreviation,Final_Admin_Numerator);
run;

/* Sorting in preparation for tranpose */
proc sort data=PartC_Compliance; by MemberXwalk TransposeKey; run;

/* Getting All Measures on 1 Line */
proc transpose data=PartC_Compliance out=PartC_Compliance_Trans;
	by MemberXwalk;
	var TransposeKey;
run;

/* Putting all measures into one column for format */
data PartC_Compliance_Trans;
	set PartC_Compliance_Trans;
	AllCompliance=catx("|",of COL:);
run;

/* Creating format */
%Formatme(work,PartC_Compliance_Trans,MemberXwalk,AllCompliance,$PartC_Compliance);

/* Attaching Part C to original list */
data PartC_Compliance_Final(keep=member_id MemberXwalk 
								 MeasureAbreviation MeasureCompliance);
	set PopHealth_Members(keep=member_id MemberXwalk);
	Compliance=put(MemberXwalk,$PartC_Compliance.);
	/* Looping through to break out records */
	do i=1 to 999;
		temp_Compliance=scan(Compliance,i,"|");
		if temp_Compliance="" then i=999;
		else do;
			MeasureAbreviation=scan(temp_Compliance,1,":");
			MeasureCompliance=input(scan(temp_Compliance,2,":"),8.);
			output;
		end;
	end;
run;


/**************************************************/
	/* Part D Compliance  */
/**************************************************/

/* Creating a Key for Transpose */
data PartD_Compliance;
	length Final_Measure_Abreviation $120.;
	set partd.final_days_covered_diab(in=Diab)
		partd.final_days_covered_ras(in=Ras)
		partd.final_days_covered_stat(in=Stat)
		partd.supd_final_detail(in=SUPD);

	/* Applying crosswalk */
	MemberXwalk=put(Mbr_ID,$xwalk.);

	/* Classifying Measures */
	if Diab=1 then Final_Measure_Abreviation="ADH - DIAB";
	else if Ras=1 then Final_Measure_Abreviation="ADH - RAS";
	else if Stat=1 then Final_Measure_Abreviation="ADH - STAT";
	else if SUPD=1 then Final_Measure_Abreviation="SUPD";

	TransposeKey=catx(":",Final_Measure_Abreviation,Compliant_Flag);
run;

/* Sorting in preparation for tranpose */
proc sort data=PartD_Compliance; by MemberXwalk TransposeKey; run;

/* Getting All Measures on 1 Line */
proc transpose data=PartD_Compliance out=PartD_Compliance_Trans;
	by MemberXwalk;
	var TransposeKey;
run;

/* Putting all measures into one column for format */
data PartD_Compliance_Trans;
	set PartD_Compliance_Trans;
	AllCompliance=catx("|",of COL:);
run;

/* Creating format */
%Formatme(work,PartD_Compliance_Trans,MemberXwalk,AllCompliance,$PartD_Compliance);

/* Attaching Part D to original list */
data PartD_Compliance_Final(keep=member_id MemberXwalk 
								 MeasureAbreviation MeasureCompliance);
	set PopHealth_Members(keep=member_id MemberXwalk);
	Compliance=put(MemberXwalk,$PartD_Compliance.);
	/* Looping through to break out records */
	do i=1 to 999;
		temp_Compliance=scan(Compliance,i,"|");
		if temp_Compliance="" then i=999;
		else do;
			MeasureAbreviation=scan(temp_Compliance,1,":");
			MeasureCompliance=input(scan(temp_Compliance,2,":"),8.);
			output;
		end;
	end;
run;


/**************************************************/
	/* Creating the Files*/
/**************************************************/

/* Chase List File */
data export_chaselist;
	length ChaseListType $20. CampaignID $50. LOB $20. PersonUniqueID $80.
		   MemberID $80. LastName $50. FirstName $50. MidInit $1. DOB 8.
		   Lang $20. Gender $1. Address $250. City $50. State $2. ZIP $10.
		   Phone $10. MobilePhone $10. OtherPhone $10. PlanID $20.
		   PCPID $120. PCPNPI $120. ProviderEntity 8. ProviderOfficeName $100.
		   PCPLastName $100. PCPFirstName $55. TaxonomyCode $10.
		   PCPAddressLine1 $55. PCPAddressLine2 $55. PCPCity $40. PCPState $2.
		   PCPZIP $5. PCPOfficePhone $10. PCPMobilePhone $10. PCPFax $10.
		   PCPWebAddress $150. PCPTaxID $10. PCPMedicareID $10. PCPSpecCode $120.
		   cRiskLevel $20. cRiskScore 5.2 cRiskScoreType $20. ClientCareProgram $150.;
	set PopHealth_Members;

	/* Note some of these may change from file to file */
	ChaseListType="XXXXXXXXXX";
	CampaignID="XXXXXXXXXX";
	PersonUniqueID=put(member_id,$MBI.);
	MemberID=member_id;

	LastName=put(MemberID,$Member_LastName.);
	FirstName=scan(put(MemberID,$Member_FirstName.),1,"");
	MidInit=scan(put(MemberID,$Member_FirstName.),2,"");
	DOB=input(put(MemberID,$Member_DOB.),8.);
	Lang=put(MemberID,$Language.);
	Gender=put(MemberID,$Member_Sex.);

	Address=put(MemberAddno,$Member_to_Address.);
	City=put(MemberAddno,$Member_to_City.);
	State=put(MemberAddno,$Member_to_State.);
	ZIP=put(MemberAddno,$Member_to_ZIP.);

	Phone=put(MemberAddno,$MemberPrimaryPhone.);
	MobilePhone="";
	OtherPhone=put(MemberAddno,$MemberSecondaryPhone.);

	Prog_=put(MemberID,$Member_to_Program.);
	if prog_="XXXXXXXXXX" then do;
		PlanID="XXXXXXXXXX"; LOB="Medicare";
	end;
	else if prog_ in ("XXXXXXXXXX","XXXXXXXXXX") then do;
		PlanID="XXXXXXXXXX"; LOB="Medicare";
	end;
	else if prog_ in ("XXXXXXXXXX") then do;
		PlanID="XXXXXXXXXX"; LOB="Medicare";
	end;
	if LOB="" then do;
		LOB="Medicare";
	end;

	PCPID=put(MemberXwalk,$Member_to_Affiliation.);
	PCP_ID=put(MemberXwalk,$Member_to_PCP.);
	PCPNPI=put(PCP_ID,$Provider_NPI.);
	ProviderEntity=1; /* Should all be people */
	ProviderOfficeName="";
	PCPLastName=put(PCP_ID,$Provider_LastName.);
	PCPFirstName=put(PCP_ID,$Provider_FirstName.);
	TaxonomyCode=put(PCPNPI,$Provider_TaxonomyCode.);

	PCPAddressLine1=put(PCP_ID,$PCP_to_Address_One.);
	PCPAddressLine2=put(PCP_ID,$PCP_to_Address_Two.);
	PCPCity=put(PCP_ID,$PCP_to_City.);
	PCPState=put(PCP_ID,$PCP_to_State.);
	PCPZIP=put(PCP_ID,$PCP_to_ZIP.);
	PCPOfficePhone=put(PCP_ID,$PCP_to_Phone.);
	PCPMobilePhone="";

	/* For PCP Fax using centralized Fax Number for Owners */
	PCPTaxID=put(MemberXwalk,$Member_to_IRS.);
	if PCPTaxID in ("XXXXXXXXXX","XXXXXXXXXX") then PCPFax="XXXXXXXXXX";
	else if PCPTaxID in ("XXXXXXXXXX","XXXXXXXXXX") then PCPFax="XXXXXXXXXX";
	else if PCPTaxID in ("XXXXXXXXXX") then PCPFax="XXXXXXXXXX";
	else if PCPTaxID in ("XXXXXXXXXX") then PCPFax="XXXXXXXXXX";
	else PCPFax=put(PCP_ID,$Provider_Fax.);

	PCPWebAddress="";
	PCPMedicareID="";
	PCPSpecCode=put(put(MemberXwalk,$Member_to_Specialty.),$spdesc.);

	cRiskLevel="";
	cRiskScore=Current_Score;
	cRiskScoreType="CMS HCC";
	ClientCareProgram="";

	keep ChaseListType CampaignID LOB PersonUniqueID MemberID LastName 
		 FirstName MidInit DOB Lang Gender Address City State ZIP
		 Phone MobilePhone OtherPhone PlanID PCPID PCPNPI ProviderEntity
		 ProviderOfficeName PCPLastName PCPFirstName TaxonomyCode
		 PCPAddressLine1 PCPAddressLine2 PCPCity PCPState PCPZIP
		 PCPOfficePhone PCPMobilePhone PCPFax PCPWebAddress PCPTaxID
		 PCPMedicareID PCPSpecCode cRiskLevel cRiskScore cRiskScoreType
		 ClientCareProgram;
	format DOB yymmdd10. cRiskScore 6.2;
run;

/* Sorting for export */
proc sort data=export_chaselist; by PersonUniqueID MemberID; run;


/* Care Gap File */
Data export_caregap;
	length PersonUniqueID $80. MemberID $80. GapType $20.
		   GapCode $20. GapDesc $100. GapCodeType 8.
		   GapSource $20. GapStatus $20. GapEventDate 8.;
	set PartC_Compliance_Final(in=PartC)
		PartD_Compliance_Final(in=PartD);

	PersonUniqueID=put(member_id,$MBI.);
	MemberID=member_id;
	GapType="Care"; /* These are all care gaps (Part C & D) */
	GapCode=""; /* Not required for Care Gaps */
	GapSource="";
	GapCodeType=.;

	/* Attaching Gap Description */
	if MeasureAbreviation="BCS-E" then GapDesc="Breast Cancer Screening";*ECDS;
	else if MeasureAbreviation="COL-E" then GapDesc="Colorectal Cancer Screening";*ECDS;
	else if MeasureAbreviation="OMW" then GapDesc="Osteoporosis Management in Women who had a Fracture";
	else if MeasureAbreviation="CDC Eye" then GapDesc="Diabetes Care - Eye Exam";
	else if MeasureAbreviation="CDC A1c" then GapDesc="Diabetes Care - Blood Sugar Controlled";
	else if MeasureAbreviation="CBP" then GapDesc="Controlling Blood Pressure";
	else if MeasureAbreviation="SPC" then GapDesc="Statin Therapy for Patients With Cardiovascular Disease";
	else if MeasureAbreviation="ADH - DIAB" then GapDesc="Medication Adherence for Diabetes Medications";
	else if MeasureAbreviation="ADH - RAS" then GapDesc="Medication Adherence for Hypertension";
	else if MeasureAbreviation="ADH - STAT" then GapDesc="Medication Adherence for Cholesterol";
	else if MeasureAbreviation="SUPD" then GapDesc="Statin Use in Persons with Diabetes Measures";
	else if MeasureAbreviation="KED" then GapDesc="Kidney Health Evaluation for Patients with Diabetes";
	else GapDesc="DESCRIPTION MISSING";

	/* Converting Compliance to a Status */
	if MeasureCompliance=1 then GapStatus="Closed";
	else if MeasureCompliance=0 then GapStatus="Open";
	
	/* Adding Date Limitation - Different depending on the measure type because
	   Part D is calculated internally */
	if PartC=1 then GapEventDate=&LimitationPartC;
	else if PartD=1 then GapEventDate=&LimitationPartD;
	
	keep PersonUniqueID MemberID GapType GapCode GapDesc GapCodeType
		 GapSource GapStatus GapEventDate;
	format GapEventDate yymmdd10.;
run;

Proc Summary Data= PartC_Compliance_Final;
 class MeasureAbreviation;
  OUtput Out= AbreviationCheck; Run;

/* Sorting for export */
proc sort data=export_caregap; by PersonUniqueID MemberID GapDesc; run;

/* Checking Gap Description Names */
proc freq data=export_caregap noprint;
	tables GapDesc/out=check_names;
run;

/* Removing the Description Missing records as they are mostly Overlapping Members
due to changes in Changes of Measure Abbriviation*/

Data Final_export_caregap; Set export_caregap;
 Where GapDesc ne 'DESCRIPTION MISSING'; Run;

Proc Summary Data=Final_export_caregap nway missing;
 Class GapDesc;
 Output Out=Description_Check; Run;

 *Create a place holder data set
 This really has nothing important in it
 I need it for the below macro check to work;
 Data placeholder;
 input VariablePH;
 cards;
 1
 run;

%Macro Checking_stuff (description);

 Data Check_Description; Set Description_Check;
 Where GapDesc = &description; run;

Data _null_; Set Check_Description placeholder nobs=num_obs;
 If num_obs = 2 Then Do;
 	End;
 Else Do;
	put 'Check Measure Mapping - Your Gap file has missing records for something';
	abort;
 	end;
 run;

%Mend;

%Checking_stuff('Breast Cancer Screening')
%Checking_stuff("Colorectal Cancer Screening")
%Checking_stuff("Osteoporosis Management in Women who had a Fracture")
%Checking_stuff("Diabetes Care - Eye Exam")
%Checking_stuff("Diabetes Care - Blood Sugar Controlled")
%Checking_stuff("Controlling Blood Pressure")
%Checking_stuff("Statin Therapy for Patients With Cardiovascular Disease")
%Checking_stuff("Medication Adherence for Diabetes Medications")
%Checking_stuff("Medication Adherence for Hypertension")
%Checking_stuff("Medication Adherence for Cholesterol")
%Checking_stuff("Statin Use in Persons with Diabetes Measures")
%Checking_stuff("Kidney Health Evaluation for Patients with Diabetes")


/**************************************************/
	/* Exporting the Files  */
/**************************************************/

/* Exporting the Chase List File */
filename Chase "&XXXXXXXXXX\XXXXXXXXXX\Medicare_&MeasurementYear._ChaseList_&logdate..txt" termstr=CRLF;
proc export data=export_chaselist
	outfile=Chase
	dbms=dlm label replace;
	delimiter='|';
run;

proc export data=WORK.EXPORT_CHASELIST
    outfile="\\XXXXXXXXXX - Emcara IHA Target and Gap File\Output\Medicare_2022_ChaseList_20230201_0705.txt"
    dbms=csv
    replace;
	delimiter='|';
run;

/* Exporting the Care Gap File */
filename CareGap "&path\Output\Medicare_&MeasurementYear._CareGap_&logdate..txt" termstr=CRLF;
proc export data=Final_export_caregap
	outfile=CareGap
	dbms=dlm label replace;
	delimiter='|';
run;