****************************************************************************************************
*                                           PROGRAM OVERVIEW
****************************************************************************************************
*
* PROGRAM: t1t2conc_createdata.sas  
* Created (mm/dd/yyyy): 05/14/2021
*
*--------------------------------------------------------------------------------------------------
* PURPOSE: The macro produces tables for a standard Type 1 and Type 2 report
*                                        
*  Program inputs:                                                                                   
*   - For Type 1 requests: agg_t1cida.sas7bdat                                               
*   - For Type 2 requests: agg_t2cida.sas7bdat
*   - For Type 2 concomitance requests: agg_t1conc.sas7bdat
* 
*  Program outputs:                                                                                                                           
*   - For Type 1 requests aggregation across all data partners: final_t1cida.sas7bdat                             
*   - For Type 2 requests aggregation across all data partners: final_t2cida.sas7bdat
*   - For Type 2 concomitance requests: final_t2conc.sas7bdat 
*   - For Type 1 requests by data partner: final_dps_t1cida.sas7bdat                             
*   - For Type 2 requests by data partner: final_dps_t2cida.sas7bdat
*   - For Type 2 concomitance requests by data partner: final_dps_t2conc.sas7bdat 
* 
*  PARAMETERS: 
*  - for t1: table =t1_cida, grpvar = group
*  - for t2: table =t2_cida, grpvar = group
*  - for conc: table =t2_conc, grpvar = analysisgrp
*            
*  Programming Notes:                                                                                
*                                                                           
*
*--------------------------------------------------------------------------------------------------
* CONTACT INFO: 
*  Sentinel Coordinating Center
*  info@sentinelsystem.org
*
***************************************************************************************************;

%macro t1t2conc_createdata(table =, grpvar = );

    %put =====> MACRO CALLED: t1t2conc_createdata ;
	
   /************************************************************************************************
      Determine levels and stratifications               
    ************************************************************************************************/	
	/* Confirm a table columns file has been specified */
	%if %str("&tablecolumnsfile.") = %str("") %then %do;
	  %put ERROR: (Sentinel) Lookup table includes dataset &tdatasetlist., but tablecolumnsfile is not specified in &createreportfile. file.;
	  %abort;
	%end;
	
	%let &table._stratification =;
	
    proc sql noprint;
       /* Determine &table. levels and stratifications to store in macro variables*/
       select distinct quote(strip(levelid1))  
	   into :&table._levelid separated by ' '
       from tablefile where dataset = "&table.";
	 
       select distinct tablesub into: &table._stratification separated by ' ' 
            from tablefile
            where tablesub ne 'overall' and dataset = "&table.";
    quit;

    /************************************************************************************************
      Collapse data - if stratifybyDP = Y, need to reclassify prior to aggregation
                      if stratifybyDP = N, collapse after aggregation 
    ************************************************************************************************/
	%if %index(&&&table._stratification,race) & "&collapse_vars." = "race" %then %do;

        /*identify sort order #*/
        data _null_;
            set stratavars_agg_&table.;
            if strata = "race" then call symputx("sortnb",_n_);
        run;

		%if &stratifybydp = Y %then %do;
        %collapse_vars(dataset=agg_&table., 
                       dpstrat=Y,
                       groupvar=&grpvar.,
                       where =level in (&&&table._levelid), 
                       list=%str("American Indian or Alaska Native", "Asian", "Black or African American", "Multi-racial", "White", "Native Hawaiian or Other Pacific Islander"),
                       unknown="Unknown", 
                       sort=&sortnb., 
                       varlist=npts episodes adjustedcodecount rawcodecount daysupp amtsupp
                              %if %index(&table,conc) = 0 %then %do;
                                 dennumpts dennummemdays timetocensor
                    		  %end;
                    		  %if %substr(&table,2,1) ne 1 %then %do;
                    		     eps_wevents all_events followuptime
                    		  %end;,
                       classlist=dpidsiteid level &grpvar. %do s = 1 %to &&numstrata_&table.; sortorder&s. %end; &&&table._stratification;);
		%end;
    %end;

    /************************************************************************************************
      Summarize data                 
    ************************************************************************************************/
    proc summary data = agg_&table. (where = (level in (&&&table._levelid))) nway missing;
        class level &grpvar. %do s = 1 %to &&numstrata_&table.; sortorder&s. %end; &&&table._stratification;
		  var npts episodes adjustedcodecount rawcodecount daysupp amtsupp
          %if %index(&table,conc) = 0 %then %do;
             dennumpts dennummemdays timetocensor
		  %end;
		  %if %substr(&table,2,1) ne 1 %then %do;
		     eps_wevents all_events followuptime
		  %end;;
        output out = agg_&table._sum (drop = _:) sum=;
    run;

    /*Collapse if stratifybyDP = N*/
	%if %index(&&&table._stratification,race) & "&collapse_vars." = "race" & &stratifybydp. = N %then %do;
        %collapse_vars(dataset=agg_&table._sum, 
                       groupvar=&grpvar.,
                       list=%str("American Indian or Alaska Native", "Asian", "Black or African American", "Multi-racial", "White", "Native Hawaiian or Other Pacific Islander"),
                       unknown="Unknown", 
                       sort=&sortnb., 
                       varlist=npts episodes adjustedcodecount rawcodecount daysupp amtsupp
                              %if %index(&table,conc) = 0 %then %do; dennumpts dennummemdays timetocensor %end;
                    		  %if %substr(&table,2,1) ne 1 %then %do; eps_wevents all_events followuptime %end;,
                       classlist= level &grpvar. %do s = 1 %to &&numstrata_&table.; sortorder&s. %end; &&&table._stratification;);
	%end;
	
   /************************************************************************************************
      Determine total count of variables on table and put tablecolumns information into macro variables             
    ************************************************************************************************/
	proc sql noprint;
	  select count(column) into: numcolumns trimmed
	  from tablecolumns where table = "&table.";
		
	  select columnname 
	        ,column 
			,columnlabel
			,columnformat
			,scan(compress(column,'()'),1,'/') as numerator
			,scan(compress(scan(column,1,'*'),'()'),2,'/') as denominator
			,scan(compress(column,'()*0123456789.'),2,'/') as cidenominator
			,scan(column,2,'*') as multiplier
			,cirate
			,footnote
	   into: var1 -:var&numcolumns.
		    ,:formula1 - :formula&numcolumns.
			,:label1 - :label&numcolumns.
			,:format1 - :format&numcolumns.
			,:num1 - :num&numcolumns.
			,:denominator1 - :denominator&numcolumns.
			,:cidenom1 - :cidenom&numcolumns.
			,:multi1 - :multi&numcolumns.
			,:cirate1 - :cirate&numcolumns.
			,:footnote1 - :footnote&numcolumns.
	  from tablecolumns where table = "&table.";
    quit;

   /************************************************************************************************
       Prepare final summary datasets           
    ************************************************************************************************/ 
    /*Macro to finalize tables*/
    %macro prept1t2data(dsin=, dsout=, dpvar=);
    	/* Check to see if POINT was specified in T2 queries */
		%if %index(&reporttype,T2) %then %let pointflag = Y;
    	%else %let pointflag = N;
    	%if %index(&dsin.,t2conc) %then %let t2group=analysisgrp;
    	%else %let t2group=group;
	    	    	
     	proc sql noprint undo_policy=none;
     		create table &dsin as 
     		select a.*
			%if %index(&dsin.,t2conc) %then %do;
				,'N' as outputdenom
			%end;
			%else %do;
				,b.outputdenom
			%end; 
			%if %index(&reporttype,T2) %then %do;
				, upper(b.point) as point
			%end;
     		from &dsin a 
     		left join master_typefile b 
     		on a.&t2group = b.group;
     	quit;

	   /* Check to see if there are 0 total patients per cohort */
	   proc sql noprint undo_policy=none;
	   	create table &dsin as 
	   	select a.*, b.totalnpts, b.totalepisodes		
	   	from &dsin a 
	   	left join (select &t2group, %if %length(&dpvar) > 0 %then %do; dpidsiteid, %end; sum(npts) as totalnpts, sum(episodes) as totalepisodes
	   			   from &dsin.
	   			   group by &t2group %if %length(&dpvar) >0 %then %do; ,dpidsiteid %end;) b
	   	on a.&t2group =b.&t2group %if %length(&dpvar) > 0 %then %do; and a.dpidsiteid = b.dpidsiteid %end; ;
	   quit;

       data _&dsout. (keep = level &grpvar. sortorder: &&&table._stratification &dpvar.
	                  %do vv = 1 %to &numcolumns; &&var&vv. &&var&vv.._char %end; );
         set &dsin.;
		 length lambda se ci_lower ci_upper p q 8;
		 call missing(lambda, se, ci_lower, ci_upper, p, q);
		/* Calculated vars and labels */
        %do vv = 1 %to &numcolumns;
          %if &&footnote&vv. > 0 %then %do;
		    label &&var&vv. = "&&label&vv.^{super 1}";
		    label &&var&vv.._char = "&&label&vv.^{super 1}";
		  %end;
		  %else %do;
		    label &&var&vv. = "&&label&vv.";
		    label &&var&vv.._char = "&&label&vv.";
		  %end;
		  
	      %if %sysfunc(index(&&formula&vv.,/)) > 0 %then %do;
			 %if %str("&&cirate&vv.") = %str("R") %then %do;
			   format &&var&vv. $30.;
			   if &&cidenom&vv.. > 0 and &&num&vv. > 0 then do;
                  lambda = &&formula&vv.;
			      se = sqrt(1/&&num&vv.);
			      ci_lower = exp(log(lambda) - 1.96 * se);
                  ci_upper = exp(log(lambda) + 1.96 * se);
			      &&var&vv. = strip(put(lambda, &&format&vv.)) || " (" || strip(put(ci_lower, &&format&vv.)) || ", " || strip(put(ci_upper, &&format&vv.)) || ")";
			      &&var&vv.._char=&&var&vv.;
			   end;
			   else if &&num&vv. = 0 and &&cidenom&vv.. > 0 then do;
			      &&var&vv. = strip(put(0, &&format&vv.)) || " (" || strip(put(0, &&format&vv.)) || ", " || strip(put(0, &&format&vv.)) || ")";
			      &&var&vv.._char=&&var&vv.;
			   end;
			   else do;
			   	&&var&vv. = "NaN";
				%if %index(%lowcase(&&formula&vv.),dennum) %then %do;
					if missing(DenNumPts) = 1 then &&var&vv..="N/A";
				%end;
			   	&&var&vv.._char=&&var&vv.;
				 %if ^%index(%lowcase(&&formula&vv.),dennum) %then %do;
					if totalnpts = 0 and &&var&vv.. = 0 then &&var&vv.._char='.';
				 %end;	
			   end;
			   %if &pointflag = Y and %sysfunc(prxmatch(m/daysupp|amtsupp/i,&&formula&vv.)) %then %do;
			     	if point = 'Y' then &&var&vv.._char='N/A';
			   %end;			   
             %end;	
			 %else %if %str("&&cirate&vv.") = %str("P") %then %do;
               format &&var&vv. $30.;			 
			   if &&cidenom&vv. > 0 then do;
                 p = %scan(&&formula&vv.,1,*);
			     q = 1 - p;
			     se = sqrt((p*q)/&&cidenom&vv.);
			     ci_lower = p - 1.96 * se;
                 ci_upper = p + 1.96 * se;
			     %if %eval(&&multi&vv. > 0) %then %do;
			       &&var&vv. = strip(put(p*&&multi&vv., &&format&vv.)) || " (" || strip(put(ci_lower*&&multi&vv., &&format&vv.)) || ", " || strip(put(ci_upper*&&multi&vv., &&format&vv.)) || ")";
			       &&var&vv.._char = &&var&vv.;
			     %end;
			     %else %do;
			       &&var&vv. = strip(put(p, &&format&vv.)) || " (" || strip(put(ci_lower, &&format&vv.)) || ", " || strip(put(ci_upper, &&format&vv.)) || ")";
			       &&var&vv.._char = &&var&vv.;
			     %end;
               end;
			   else do;
			   	&&var&vv. = "NaN";
				%if %index(%lowcase(&&formula&vv.),dennum) %then %do;
					if missing(DenNumPts) = 1 then &&var&vv..="N/A";
				%end; 
			   	&&var&vv.._char=&&var&vv.;
				 %if ^%index(%lowcase(&&formula&vv.),dennum) %then %do;
					if totalnpts = 0 and &&var&vv.. = 0 then &&var&vv.._char='.';
				 %end;			
			   end;
			   %if &pointflag = Y and %sysfunc(prxmatch(m/daysupp|amtsupp/i,&&formula&vv.)) %then %do;
			     	if point = 'Y' then &&var&vv.._char='N/A';
			   %end;
			 %end;
			 %else %do;
			   format &&var&vv. &&format&vv.;
			   if &&denominator&vv. > 0 then do;
			   	&&var&vv. = &&formula&vv.;
			   	&&var&vv.._char=strip(put(&&var&vv.,&&format&vv.));
				 %if ^%index(%lowcase(&&formula&vv.),dennum) %then %do;
					if totalnpts = 0 and &&var&vv.. = 0 then &&var&vv.._char='.';
				 %end;
			   end;
			   else do;
			   	&&var&vv. =0;
			   	&&var&vv.._char="NaN";
				%if %index(%lowcase(&&formula&vv.),dennum) %then %do;
					if missing(DenNumPts) = 1 then &&var&vv..="N/A";
				%end;
				 %if ^%index(%lowcase(&&formula&vv.),dennum) %then %do;
					if totalnpts = 0 and &&var&vv.. = 0 then &&var&vv.._char='.';
				 %end;	
			   end;
				 %if &pointflag = Y and %sysfunc(prxmatch(m/daysupp|amtsupp/i,&&formula&vv.)) %then %do;
			     	if point = 'Y' then &&var&vv.._char='N/A';
			     %end;
			 %end;
		  %end;
		  %else %do;
		     format &&var&vv. &&format&vv.;
		     &&var&vv. = &&formula&vv.;
		     &&var&vv.._char=strip(put(&&var&vv.,&&format&vv.));
		     %if &pointflag = Y and %sysfunc(prxmatch(m/daysupp|amtsupp/i,&&formula&vv.)) %then %do;
		     	if point = 'Y' then &&var&vv.._char='N/A';
		     %end;
			 %if ^%index(%lowcase(&&formula&vv.),dennum) %then %do;
				if totalnpts = 0 and &&var&vv.. = 0 then &&var&vv.._char='.';
			 %end;
		  %end;

		  %if %index(%lowcase(&&formula&vv.),dennum) %then %do;
		  	if upcase(outputdenom) = "N" then &&var&vv.._char='N/A';
			%if %index(%lowcase(&&formula&vv.),dennummemdays) %then %do;
				if upcase(outputdenom) = "M" then &&var&vv.._char='N/A';
			%end;
		  %end;

		  %if %index(%lowcase(&&formula&vv.),npts) or %index(%lowcase(&&formula&vv.),episodes) %then %do;
		  	%if ^%index(%lowcase(&&formula&vv.),/) and ^%index(%lowcase(&&formula&vv.),episodes)  %then %do;
		  	if totalnpts = 0 then &&var&vv.._char='0';
		  	%end;
		  	%else %do;
		  	if totalnpts = 0 or totalepisodes = 0 then &&var&vv.._char = '.';
		  	%end;
		  %end;
		  %else %do;
		  if totalnpts = 0 or totalepisodes = 0 then &&var&vv.._char='.';
		  %end;	
		  %if %sysfunc(prxmatch(m/dennumpts|dennummemdays/i,&&formula&vv.)) %then %do;
			if upcase(outputdenom) ^= 'M' and (missing(dennumpts) or missing(dennummemdays)) then &&var&vv.._char='N/A';
			else if missing(dennumpts) then &&var&vv.._char='N/A';
	      %end;	  
	    %end;
		
        /*labels for stratification variables*/
        %if %index(&&&table._stratification,state) %then %do;
            if state in ("Invalid", "Missing") and episodes lt 1 then delete;
			if state = "Invalid" then sortorder_state = "ZY";
			else if state = "Missing" then sortorder_state = "ZZ";
			else sortorder_state = state;
        %end;

        label
              %if %index(&&&table._stratification,sex) %then %do;
              sex = "Sex"
              %end;
              %if %index(&&&table._stratification,agegroup) %then %do;
              Agegroup = "Age Group"
              %end;
              %if %index(&&&table._stratification,year) %then %do;
              year = "Year"
              %end;
              %if %index(&&&table._stratification,month) %then %do;
              month = "Month"
              %end;
			  %if %index(&&&table._stratification,quarter) %then %do;
              quarter = "Quarter"
              %end;
              %if %index(&&&table._stratification,race) %then %do;
              race = "Race"
              %end;
              %if %index(&&&table._stratification,state)  %then %do;
              state = "State"
              %end;
              %if %index(&&&table._stratification,hhs_reg) %then %do;
              hhs_reg = "Health and Human Services (HHS) Region"
              %end;
              %if %index(&&&table._stratification,cb_reg) %then %do;
              cb_reg = "Census Bureau Region"
              %end;
              %if %index(&&&table._stratification,zip3) %then %do;
              zip3 = "3-Digit Zip Code/State"
              %end;
              %if %index(&&&table._stratification,zip_uncertain) %then %do;
              zip_uncertain = "Zip Uncertain"
              %end;
              %if %index(&&&table._stratification,hispanic) %then %do;
              hispanic = "Hispanic Origin"
              %end;;

             /*covariate*/
             %if &numsummarystratcovars. > 0 %then %do;
               %do c = 1 %to &numsummarystratcovars.;
                  %if %index(&&&table._stratification,&&covar&c..) %then %do;
				    length _&&covar&c. $%eval(&baselinelabellength + 15);
                    label _&&covar&c. = "&&covar&c.";
                    if &&covar&c.. = 0 then _&&covar&c. = "No evidence of &&&&study&&covar&c..";
                    if &&covar&c. = 1 then _&&covar&c. = "Evidence of &&&&study&&covar&c.."; 
					drop &&covar&c.;
                    rename _&&covar&c. = &&covar&c.;
                  %end;
               %end;
             %end;
            ;                
        run;

		/* Apply labels */
		%isdata(dataset=labelfile);
	
        proc sql noprint;
          create table &dsout. as
          select a.*, b.order
		  %if %eval(&nobs.>0) %then %do;
		    ,d.label as header 
			,case when c.label = "" then a.&grpvar.
             else c.label end as grouplabel 
		  %end;
          %else %do;
            ,"" as header 
			,a.&grpvar. as grouplabel 
           %end;				   
          from _&dsout. a 
		  left join groupsfile b
		  on strip(lowcase(a.&grpvar.)) = strip(lowcase(b.group))
		  %if %eval(&nobs.>0) %then %do;
		    left join labelfile (where = (labeltype = "grouplabel")) c
		    on strip(a.&grpvar.) = strip(c.group)
		    left join labelfile (where = (labeltype = "header")) d
		    on strip(a.&grpvar.) = strip(d.group)
		  %end;;
        quit;
    %mend;

    /*Overall*/
    %prept1t2data(dsin=agg_&table._sum, dsout=final_&table.);
	
	%if &stratifybydp. = Y %then %do;
	  %prept1t2data(dsin=%str(agg_&table. (where = (level in (&&&table._levelid)))), dsout=final_dps_&table., dpvar=dpidsiteid);
	%end;

    %put =====> END MACRO: t1t2conc_createdata ;

%mend t1t2conc_createdata;