/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
Macro's usage Note: 

When having defined a template for the information that is going to be processed.  
  Make the macro call's to preparation and wrk for the wanted transposed to deliver using the given selection of enhanced input data. 
   -->   ysemdbnk_pre (state_type_id=...., val_semdbnk=...., tabletype_sel=.... )  ; 
   -->   ysemdbnk_wrk (state_type_id=...., val_semdbnk=...., tabletype_sel=.... )  ; 

Notes:
 - KSADSEM is the location of enhanced selected data elements 
      &val_semdbnk an options to support multiple versions and/or muliple domains of ehanced elements of data.
 - KSADBNK the location of monitoring logging implemented in this code for functional purposes.
 - KSA_NRM where the private metadata is stored with possible some other basic standaards   
      &val_subnrm an options to support multiple versions and/or muliple domains of private metadata.
 
 - KSADSEM variables: 
     tabletype_sel is during the enhancement step (kwdrnt1) filled with the name of the intended table.  
     state_entity_idaggr, logproces_entity_id (key state_entity_id and date) are added ehanced values by a lookup process.  
 - KSA_NRM where the defined calculations for elements are stored an externsion to private metadata   
   
 
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

 
/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
/* * * ykwdrnt2 ysemdbnk_pre                                                               * * * */ 
/* * * Define elements relevant to become transposed into columns                          * * * */ 
/* * * Only vcd_spec3 allowed to be blank. Temporary replaced by a dot avoiding it missing * * * */ 
/* * * Use only low case names as new names for columns                                    * * * */ 
/* * * Have the calculations on transposed columns defined                                 * * * */ 
/* * * Keep a logical order in transposed columns                                          * * * */ 
/* * * Save all these definitons in a "def" file for the actual tranpose work              * * * */ 
 
%macro ysemdbnk_pre(opt=,state_type_id=, val_semdbnk=,val_subnrm=, tabletype_sel=) ;

%let state_databank_idmut=%sysfunc(datetime());

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
   Opbouw variabelen uit semantic selectie en calculation normen   1,2,3 
   welke variabelen als vraag komt mee met een telling  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
proc means data=KSADSEM.&state_type_id._VALUES&val_semdbnk. (where=(logproces_tabletype_id="&tabletype_sel") ) noprint ;   
 class vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar ;
 var vcd_value_flt ;
 output out=&state_type_id._VALUES&val_semdbnk._cnt n=varcount;
run;

%put DBNK NOTE: Elements present in the ehanced data are counted only those having real values are relevant.   %sysfunc(datetime(),datetime.)  ;
data KSADSEM.&state_type_id._VALUES&val_semdbnk._cnt ; 
   Set &state_type_id._VALUES&val_semdbnk._cnt ( where= ( vcd_spec1_idvar ne "" and vcd_spec2_idvar ne "" and vcd_spec3_idvar ne "" ) ) ;
   length vcd_variable $32 ;
   if (vcd_spec3_idvar  in(" ","." ) ) then vcd_spec3_idvar="" ;  /* the dot is missing avoidance */   
   vcd_spec1_idvar=lowcase(vcd_spec1_idvar);
   vcd_spec2_idvar=lowcase(vcd_spec2_idvar);
   vcd_spec3_idvar=lowcase(vcd_spec3_idvar);
   vcd_variable=cats(vcd_spec1_idvar,"_",vcd_spec2_idvar,"_",vcd_spec3_idvar)  ; 
run;

%put %sysfunc(datetime(),datetime.) maak lijst over enkel aanwezige variabelen;
data KSADSEM.&state_type_id._VALUES&val_semdbnk._cal ; 
   Set KSA_NRM.KSN_vcd_speccal end=end ;
   length vcd_variable $32  vcd_calculation_txt $1024 ;
   if ( _N_ =1 ) then do; 
      dcl hash hvar(dataset:"KSADSEM.&state_type_id._VALUES&val_semdbnk._cnt", multidata: 'n'); 
      hvar.defineKey('vcd_variable');   
      hvar.defineDone();
   end; 
   var_fnd=0; var_cnt=1 ;
   vcd_variable=lowcase(scan(vcd_chkvarlst_txt,var_cnt)) ;  
   do while ( length( vcd_variable ) > 0 and var_cnt < 999 ) ; 
      rc = hvar.find();
      vcd_variable=lowcase(scan(vcd_calvarlst_txt,var_cnt)) ;  
      if (vcd_nacalc_txt = "bal" ) Then do;
         if (rc = 0) then do;
            if (var_fnd = 0) then do;
               vcd_calculation_txt=vcd_variable;
               var_fnd=1 ;
            end; 
            else do;
               vcd_calculation_txt=cats(vcd_calculation_txt," - ",vcd_variable);
            end;
         end;
      end;
      if (vcd_nacalc_txt = "sum" ) Then do;
         if (rc = 0) then do;
            if (var_fnd = 0) then do;
               vcd_calculation_txt=cats(" sum (",vcd_variable);
               var_fnd=1 ;
            end; 
            else do;
               vcd_calculation_txt=cats(vcd_calculation_txt," , ",vcd_variable);
            end;
         end;
      end;
      var_cnt=var_cnt+1 ;
      vcd_variable=lowcase(scan(vcd_chkvarlst_txt,var_cnt)) ;
   end; 
   if (var_fnd=1 ) then do; 
/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
   Do only calaculations that have validated present elements. 
   Attention: The vcd_variable_name is getting the new name defined (a change by naming convetion)   
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
      if (vcd_nacalc_txt = "bal" ) Then vcd_calculation_txt=cats(vcd_calculation_txt,"  ; ");
      if (vcd_nacalc_txt = "sum" ) Then vcd_calculation_txt=cats(vcd_calculation_txt," ) ; ");
      vcd_variable=cats(vcd_spec1_idvar,"_",vcd_spec2_idvar,"_",vcd_spec3_idvar)  ; 
     output; 
   end;  
run;

%put DBNK NOTE: verify existance of elements in private metadata, write the defnition  dataset %sysfunc(datetime(),datetime.) ;
Data KSADBNK.&tabletype_sel.&val_semdbnk._def (
        keep=vcd_variable vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_periodcnt_flt  vcd_units_txt vcd_description_txt vcd_calculation_txt vcd_orderdef_flt vcd_orderview_flt vcd_sortview_txt )  ;
   set KSADSEM.&state_type_id._VALUES&val_semdbnk._cnt (in=invcd) 
       KSADSEM.&state_type_id._VALUES&val_semdbnk._cal (in=incal)   ;  
   length vcd_sortview_txt $64 ;
   if ( vcd_spec3_idvar = "." ) then vcd_spec3_idvar = " " ;
   if (invcd) then set KSA_nrm.KSN_vcd_specper&val_subnrm. (keep=vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_periodcnt_flt vcd_units_txt vcd_description_txt) key=vcd_spec_idvar /unique;
   vcd_orderdef_flt = _N_ ;  
   vcd_orderview_flt = . ; 
   vcd_sortview_txt="                                                                                                            ";
   substr(vcd_sortview_txt,1,7) = substr(vcd_spec1_idvar,1,3)||substr(vcd_spec1_idvar,5,4);
   substr(vcd_sortview_txt,10,7) = substr(vcd_spec2_idvar,2,7);
   substr(vcd_sortview_txt,20,8) = substr(vcd_spec3_idvar,1,8);
   substr(vcd_sortview_txt,30,1) = substr(vcd_spec2_idvar,1,1);
   substr(vcd_sortview_txt,32,1) = substr(vcd_spec1_idvar,4,1);
run;
proc sort; by vcd_sortview_txt;
run;
Data KSADBNK.&tabletype_sel.&val_semdbnk._def ; 
   Set KSADBNK.&tabletype_sel.&val_semdbnk._def ; 
   vcd_orderview_flt = _N_ ; 
run;
proc datasets library=KSADBNK nolist nodetails;
  modify &tabletype_sel.&val_semdbnk._def ; 
  index create vcd_orderview_flt vcd_orderdef_flt  ;  
run;
     
%put DBNK NOTE: preparations for transpose and calculations is done   %sysfunc(datetime(),datetime.) ;
%mend;


/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
/* * * ykwdrnt2 ysemdbnk_wrk                                                               * * * */ 
/* * * Define elements relevant to become transposed into columns                          * * * */ 
/* * * Only vcd_spec3 allowed to be blank. Temporary replaced by a dot avoiding it missing * * * */ 
/* * * USae only low case names as new names for columns                                   * * * */ 
/* * * Have the calculations on transposed columns defined                                 * * * */ 
/* * * Keep a logical order in transposed columns                                          * * * */ 
/* * * Save all these definitons in a "def" file for the actual tranpose work              * * * */ 


%macro kosta_semdbnk_wrk(opt=, state_type_id=, val_semdbnk=,transpose=vcd,tabletype_sel=) ;
%global state_databank_idmut ;
%let state_databank_idmut=%sysfunc(datetime());


%put WBNK NOTE: Convert the content of the def dataset into macro variables %sysfunc(datetime(),datetime.) ;
data work._null_ ;  
  set KSADBNK.&tabletype_sel.&val_semdbnk._def  end=end nobs=nobs ;
  by vcd_orderview_flt ;
  length macvar_nam macvar_txt $32 chrnum $8;
  chrnum =trim(put(_N_,4.)) ;
  vcd_description_txt=translate(vcd_description_txt,"--..","&%'""") ;
  macvar_nam = cats("varview_nam_",chrnum) ;  call symputx(macvar_nam,vcd_variable ,"G" )  ;  
  macvar_txt = cats("varview_lbl_",chrnum) ;  call symputx(macvar_txt,vcd_description_txt ,"G" ) ;
  IF (end) then call symputx("variable_cnt",put(nobs,4.),"G"  ) ;
run;

data work._null_ ;  
  set KSADBNK.&tabletype_sel.&val_semdbnk._def  end=end nobs=nobs ;
  by vcd_orderdef_flt ;
  length macvar_nam macvar_nam macvar_cal macvar_vr1 macvar_vr2 macvar_vr3 $32 chrnum $8;
  chrnum =trim(put(_N_,4.)) ;
  macvar_vr1 = cats("vardef_vr1_",chrnum) ;  call symputx(macvar_vr1,vcd_spec1_idvar ,"G" )  ;  
  macvar_vr2 = cats("vardef_vr2_",chrnum) ;  call symputx(macvar_vr2,vcd_spec2_idvar ,"G" )  ;  
  macvar_vr3 = cats("vardef_vr3_",chrnum) ;  call symputx(macvar_vr3,vcd_spec3_idvar ,"G" )  ;  
  macvar_nam = cats("vardef_nam_",chrnum) ;  call symputx(macvar_nam,vcd_variable ,"G" )  ;  
  macvar_cal = cats("vardef_cal_",chrnum) ;  call symputx(macvar_cal,vcd_calculation_txt ,"G" ) ;
run;

%put  WBNK NOTE: sort the semantic datasets in a way the first last will have a windows boundary  %sysfunc(datetime(),datetime.) ;
proc sort in=KSADSEM.&state_type_id._VALUES&val_semdbnk. (where=(logproces_tabletype_id="&tabletype_sel") ) out=&state_type_id._VALUES&val_semdbnk. ;
  by  descending state_type_idjmd descending logproces_entity_idjmd state_type_id state_entity_idaggr state_entity_id logproces_entity_id  vcd_spec1_idvar vcd_spec2_idvar ;
run;

%put WBNK NOTE: build transposed dataset conforming prepared definitions. Copy valaus, add calculations. %sysfunc(datetime(),datetime.)  ;
Data KSADBNK.&tabletype_sel.&val_semdbnk. (drop=vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar  vcd_value_flt vcd_value_txt )  ;
  length logproces_tabletype_id $32 state_type_idjmd logproces_entity_idjmd $8 state_entity_id logproces_entity_id $4 state_entity_idaggr $16 state_digestion_idmut state_semantic_idmut state_databank_idmut 8 ;
  set &state_type_id._VALUES&val_semdbnk. ;
  by descending state_type_idjmd descending logproces_entity_idjmd state_type_id state_entity_idaggr state_entity_id logproces_entity_id  ;
  if ( vcd_spec3_idvar = "." ) then vcd_spec3_idvar = "";  
  format state_databank_idmut datetime. ;
  state_databank_idmut = &state_databank_idmut. ;

  /* keep values as new columns until the end of the window for all elements getting processed. */  
  retain  
  %do icnt=1 %to &variable_cnt ;
    &&varview_nam_&icnt
  %end; 
  ;
  label  
  %do icnt=1 %to &variable_cnt ;
    &&varview_nam_&icnt = "&&varview_lbl_&icnt" 
  %end; 
  ;

  /* The fist event of a Window of elements sets all new to tranpose or calculate columns to unknown (missing) */  
  if ( first.state_type_id or first.state_type_idjmd or first.state_entity_id  or 
       first.logproces_entity_idjmd  or first.logproces_entity_id ) then do;
  %do icnt=1 %to &variable_cnt ;
    &&vardef_nam_&icnt = . ; 
  %end; 
  end;
  
  /* Copy the value of an element to the destiniation new column on the condition of equale names. */  
  %do icnt=1 %to &variable_cnt ;
  if (vcd_spec1_idvar="&&vardef_vr1_&icnt" and vcd_spec2_idvar="&&vardef_vr2_&icnt" and trim(vcd_spec3_idvar)="&&vardef_vr3_&icnt" and "&&vardef_cal_&icnt" = ""  ) THEN &&vardef_nam_&icnt = vcd_value_flt;
  %end; 
 
  /* Write completed new (transposed) record with the defined calculations */  
  if ( last.state_type_id or last.state_type_idjmd or last.state_entity_id  or 
       last.logproces_entity_idjmd  or last.logproces_entity_id ) then do;
  %do icnt=1 %to &variable_cnt ;
    %if ( %length(&&vardef_cal_&icnt) > 0 ) %then %do;
  &&vardef_nam_&icnt = &&vardef_cal_&icnt;
    %end; 
  %end; 
     val_gnum = 1 ; /* teller voor measn als sum */
     output ;
  end;
run;

%put WBNK NOTE: The transposed dataset is defined. Do aggregations -SUM- on the entity groups %sysfunc(datetime(),datetime.)  ;
proc means data=KSADBNK.&tabletype_sel.&val_semdbnk. noprint ;
 by descending state_type_idjmd descending logproces_entity_idjmd state_type_id state_entity_idaggr ;
 variables val_gnum
  %do icnt=1 %to &variable_cnt ;
    &&varview_nam_&icnt 
  %end; 
 ;
 output out=KSADBNK.&tabletype_sel.&val_semdbnk._entsum sum=val_gnum / autoname ;
run;

proc means data=KSADBNK.&tabletype_sel.&val_semdbnk. noprint ;
 by descending state_type_idjmd descending logproces_entity_idjmd state_type_id  ;
 variables val_gnum
  %do icnt=1 %to &variable_cnt ;
    &&varview_nam_&icnt 
  %end; 
 ;
 output out=KSADBNK.&tabletype_sel.&val_semdbnk._sum sum=val_gnum / autoname ;
run;

%put WBNK NOTE: define indices on the created datasets for using tabele lookups when needed.  %sysfunc(datetime(),datetime.)  ;
proc datasets library=KSADBNK nolist nodetails ;
  modify &tabletype_sel.&val_semdbnk. ;
  index create refsum_id=(state_type_idjmd logproces_entity_idjmd state_type_id state_entity_id logproces_entity_id ) /unique ;
  modify &tabletype_sel.&val_semdbnk._sum ;
  index create refsum_id=(state_type_idjmd logproces_entity_idjmd state_type_id ) /unique ;
run;

%mend;