****************************************************************************************************
*                                           PROGRAM OVERVIEW
****************************************************************************************************
*
* PROGRAM: drugclass_lookup.sas  
*
* Created (mm/dd/yyyy): 07/21/14
* Last modified: 11/01/2020
* Version: 3.1
*
*--------------------------------------------------------------------------------------------------
* PURPOSE:
*   This program will create the drugclass.sas7bdat table to accompany QRP                                
*   
*  Program inputs:                                                                                   
* 	  - drug_ndc_summary.sas7bdat from 'J:\RSDC\FDB_summary_tables\'
* 	  - etc_to_generic.sas7bdat from 'J:\RSDC\FDB_summary_tables\'
*
*  Program outputs:                                                                                                                                       
*	  - drugclass
*	  - drugclass_final
*	  - drugclass_orig
*	  - drugclass_summary
*	  - generic_crosswalk
*	  - classname_crosswalk
*                                                                                                  
*  Programming Notes:                                                                                
*                                                                           
*
*--------------------------------------------------------------------------------------------------
* CONTACT INFO: 
*  Sentinel Coordinating Center
*  info@sentinelsystem.org
*
*--------------------------------------------------------------------------------------------------
*  CHANGE LOG: 
*
*   Version   Date       Initials      Comment (reference external documentation when available)
*   -------   --------   --------   ---------------------------------------------------------------
*     1.0     07/21/14     NN       Initial coding of lookup table
*
*	  1.1	  08/29/14	   JR		Modified to make unique on NDC by combining multiple classnames
*									per NDC into 1 concactenated classname
*
*	  2.0	  06/26/15	   AP		Modified to allow for routine runs (write log to external file,
*									naming of files
*									Added several QC checks
*
*	  2.1	  03/09/2016   AP		Modified to only output drugclass file and crosswalks	
*
*	  3.0	  02/11/2019   AP		Modified for integration into createlookupfiles package
*
*	  3.1	  11/01/2020   AP		Changed NDC to RX in final tables
*
***************************************************************************************************;

%macro drugclass_lookup();

	%put =====> MACRO CALLED: drugclass_lookup v3.1;

	/*----------------------------------------------------------------------------*/
	/* 1: Set up input/output parameters, confirm source file unique on NDC		  */
	/*----------------------------------------------------------------------------*/

	/* -----pull last modified date to apprend to output file names----- */
	filename fileref "&FDBPATH./drug_ndc_summary.sas7bdat";
	data _null_;
	  infile fileref truncover;
	  fid=fopen('fileref');                                                                              
	  moddate=substr(finfo(fid,'Last Modified'),1,9);
	  call symput('moddate',trim(moddate));
	run;

	libname fdb "&FDBPATH." access=readonly;

	/*---- confirm that the NDC source file only contains unique NDCs (i.e. no dups) ------*/
	/*---- confirm that the NDC source file does not contain missing Generic Name	 ------*/
	proc sort data=fdb.drug_ndc_summary out=drug_ndc_summary nodupkey DUPOUT=ndc_nodup;
		by ndc;
	run;

	data generic_test; set drug_ndc_summary;
		where missing(gnn_generic_nm);
	run;

	%macro warn(ds=, var=, message=);
		%let dsid=%sysfunc(open(WORK.&ds., i));
		%if (&dsid ne 0) %then %do;
			%let &var. = %sysfunc(attrn(&dsid, NOBS));
			%if %eval(&&&var. > 0) %then %do;
				%put WARNING: &message.;
			%end;
			%let close=%sysfunc(close(&dsid));
		%end;
	%mend;

	%warn(ds=ndc_nodup, var=ndcdupobs, message=Duplicate NDCs in source data);
	%warn(ds=generic_test, var=missgeneric, message=Missing generic names in source data);

	/*--------------------------------------------------------------------------------------------*/
	/* 2: Merge source files and create concatenated classname	 								  */
	/*--------------------------------------------------------------------------------------------*/	

	/*------ MERGE 2 FDB FILES BY GENERIC SEQUENCE NUMBER -------*/
	proc sql;
	     create table drugclass_orig as
	     select distinct a.ndc as rx label='rx'
			     	   , a.gnn_generic_nm as generic label='generic'
	                   , b.etc_name3 as classname label='classname'
		 from drug_ndc_summary as a, fdb.etc_to_generic as b
		 where a.gcn_seqno = b.gcn_seqno and upcase(etc_name1) not like 'MEDICAL SUPPL%'
	          and (a.OBSDTEC_NDC_OBSOLETE_DT GE '01JAN2000'D or missing(a.OBSDTEC_NDC_OBSOLETE_DT)) and not missing(classname);
	quit;

	/*------ CALCULTATE THE MAX LENGTH OF THE CONCATENATED CLASSNAME -------*/
	proc sql noprint ;
		select distinct sum(sum(length(classname),1))-1 as long into :theLength
		from drugclass_orig
		group by rx
		order by long DESCENDING;
	quit;

	%put "the max length in this dataset is &theLength.";

	/*------ USE THE MACRO VARIABLE "theLength" FOR CUSTOMIZING THE LENGTH OF THE VAR CLASSNAME -------*/
	data drugclass_final (keep=rx generic classname);
		set drugclass_orig (rename=(classname=x));
		by rx;
		length classname $&theLength;
		retain classname;

		if first.rx AND last.rx then classname=trim(x);
		else if first.rx then classname=trim(x);
		else classname=compbl(classname||" / "||trim(x));
		if last.rx then output;
	run;

	/*---- Confirm drugclass_final is distinct on RX and classname not missing*/

	data rx_dups; 
		set drugclass_final; 
		by rx; 
		if not(first.rx and last.rx) then output; 
	run;

	data classname_miss; 
		set drugclass_final; 
		where missing(classname);
	run;

	%warn(ds=rx_dups, var=rx_dups, message=Duplicate RXs in drugclass_final);
	%warn(ds=classname_miss, var=classname_miss, message=Missing classnames in drugclass_final);

				
	/*--------------------------------------------------------------------------------------------*/
	/* 3: Mask generic and classname							 								  */
	/*--------------------------------------------------------------------------------------------*/	

	/* ---- Get all unique class and generic names ----* */

	proc summary missing nway data = drugclass_final ;
	class generic ;
	output out = unique_generic (drop = _: rename = (generic = generic_desc)) ;
	run ;

	data dplocal.generic_crosswalk(label="Modification Date &moddate")  ;
	  set unique_generic ;
	  generic = compress('G'||put(_n_, best.)) ;
	run; 

	proc summary missing nway data = drugclass_final ;
		class classname ;
		output out = unique_classname (drop = _: rename = (classname = classname_desc)) ;
	run ;

	data dplocal.classname_crosswalk(label="Modification Date &moddate") ;
	  set unique_classname ;
	  classname = compress('C'||put(_n_, best.)) ;
	run; 

	/*----- Recreate the drugclass file with code identifiers instead of actual names ------*/

	proc sort data = drugclass_final out = generic_sort (rename = (generic = generic_desc)) ;
		by generic ;
	run ;

	data drugclass_generic ;
	  merge generic_sort (in = a)
	        dplocal.generic_crosswalk (in = b) ;
	  by generic_desc ;
	  if a ;
	  if ^b then ABORT ;
	run ;

	proc sort data = drugclass_generic out = classname_sort (rename = (classname = classname_desc)) ;
		by classname ;
	run ;

	data drugclass_generic_classname ;
	  merge classname_sort (in = a)
	        dplocal.classname_crosswalk (in = b) ;
	  by classname_desc ;
	  if a ;
	  if ^b then ABORT ;

	  drop generic_desc classname_desc ;
	run ;

	proc sort data = drugclass_generic_classname out = msoc.drugclass(label="Modification Date &moddate") ;
		by rx ;
	run ;

	/*--------------------------------------------------------------------------------------------*/
	/* 4: QC resultant files to ensure no codes were lost		 								  */
	/*--------------------------------------------------------------------------------------------*/	

	proc summary missing nway data = drugclass_final noprint;
		class generic ;
		output out = qc_generic1 ;
	run ;

	proc summary missing nway data = msoc.drugclass noprint ;
		class generic ;
		output out = qc_generic2 ;
	run ;


	proc summary missing nway data = drugclass_final noprint ;
		class classname ;
		output out = qc_classname1 ;
	run ;

	proc summary missing nway data = msoc.drugclass noprint ;
		class classname ;
		output out = qc_classname2 ;
	run ;

	/*---- Lastly create a summary file with no RX ----*/
	proc summary missing nway data = drugclass_final  ;
		class generic classname ;		
		output out = dplocal.drugclass_summary(label="Modification Date &moddate" drop = _:) ;		
	run; 
	  
	%put =====> END MACRO: drugclass_lookup v3.1;

%mend;