qrp_report

****************************************************************************************************

*                                           PROGRAM OVERVIEW

****************************************************************************************************

*

* PROGRAM: t4tables_createdata.sas  

* Created (mm/dd/yyyy): 08/06/2021

*

*--------------------------------------------------------------------------------------------------

* PURPOSE: The macro produces tables for a Type 4 report

*                                        

*  Program inputs:                                                                                   

*   - agg_t4preg.sas7bdat   

*   - agg_t4nopreg.sas7bdat 

*

*  Program outputs:                                                                                                                           

*   - final_t4moi - levelid and moiname stratification across data partners

*   - final_dps_t4moi - levelid and moiname stratification by data partner

* 

*  PARAMETERS: 

* 

*  Programming Notes:                                                                                 

​

*

*--------------------------------------------------------------------------------------------------

* CONTACT INFO: 

*  Sentinel Coordinating Center

*  info@sentinelsystem.org

*

***************************************************************************************************;

​

%macro t4tables_createdata();

​

    %put =====> MACRO CALLED: t4tables_createdata ;

    /************************************************************************************************

      Determine levels        

     ************************************************************************************************/  

      %let t4preglevel1 =;

      %let t4preglevel2 =;

      %let t4nopreglevel1 =;

      %let t4nopreglevel2 =;

      %do ds = 1 %to  %sysfunc(countw(&datasetlist,' '));

        %let t4dset = %sysfunc(scan(&datasetlist,&ds.,' '));

        proc sql noprint;

           select distinct quote(levelid1), quote(levelid2) 

           into :&t4dset.level1,

                :&t4dset.level2

           from tablefile where dataset = "&t4dset.";

        quit;

      %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;

      select columnname 

            ,column 

            ,columnlabel

            ,columnformat

            ,scan(compress(column,'()'),1,'/') as numerator

            ,cats("den_",scan(compress(scan(column,1,'*'),'()'),2,'/')) as denominator

            ,case when index(columnformat,'$') > 0 then columnformat

               else compress('$'||put(input(scan(compress(columnformat,'','a'),1,'.'),3.) +  input(scan(compress(columnformat,'','a'),2,'.'),3.),8.)||".") end as columnformatchar

       into: var1 -:var&numcolumns.

            ,:formula1 - :formula&numcolumns.

            ,:label1 - :label&numcolumns.

            ,:format1 - :format&numcolumns.

            ,:num1 - :num&numcolumns.

            ,:denominator1 - :denominator&numcolumns.

            ,:formatchar1 - :formatchar&numcolumns.

      from tablecolumns;

      select distinct(a.numerator)

             into: sumcolumns separated by " "

            from (select case when index(column,'/') > 0 then scan(compress(column,'()'),1,'/')

                   else column end as numerator from tablecolumns) a;

    quit;

    /************************************************************************************************

     Determine denominators for percent calculations          

    ************************************************************************************************/

    %macro t4_preg_nopreg (dsin = );    

       proc sql noprint undo_policy=none;

          create table _&dsin as 

              select b.*

              ,a.episodes as den_episodes

              ,a.episodes_3trim as den_episodes_3trim

              from agg_t4&dsin (keep = episodes episodes_3trim level group dpidsiteid