/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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=...., val_subsel=.... ) ; Notes: - KSADSTG the staging location having partitioned dasets servicing for history legacy and recent versions - 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. - 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 time adjsuted values for entity are kept - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ /* * * ysemdbnk Build staged data into a more semantic (enhanced) version * * * */ /* * * Logic: * * * */ /* * * From partioned staging storage choice to select default all or named versions * * * */ /* * * Add the wanted table name for elements as columns * * * */ /* * * Do period adjsutments form attribute identifications * * * */ /* * * Add adjusted indentity values by doing a lookup * * * */ /* * * Corrections for invalid, not applicable, data in staging deliveries * * * */ /* * * Value adjustment for unit changes * * * */ %macro ysemdbnk (opt=,state_id=, val_semdbnk=, val_vcdspec=, val_subsel= ) ; %let state_semantic_idmut=%sysfunc(datetime()); %put SEMD NOTE: Enahcing staging dataset to a more semantic version: KSADSEM.&state_id._VALUES&val_semdbnk. %sysfunc(datetime(),datetime.) ; Data KSADSEM.&state_id._Err_VALUES&val_semdbnk. (keep=state_type_id state_entity_id state_entity_idvrs state_type_idymd state_digestion_idmut logproces_tabletype_id logproces_entity_id state_entity_idaggr logproces_entity_idymd state_semantic_idmut vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar vcd_value_flt vcd_value_txt _errval) KSADSEM.&state_id._Err_LKUPentity&val_semdbnk. (keep=state_type_id state_entity_id state_entity_idvrs state_type_idymd state_digestion_idmut logproces_tabletype_id logproces_entity_id state_entity_idaggr logproces_entity_idymd state_semantic_idmut vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar vcd_value_flt vcd_value_txt _errval) KSADSEM.&state_id._Err_LKUPVCD&val_semdbnk. (keep=state_type_id state_entity_id state_entity_idvrs state_type_idymd state_digestion_idmut logproces_tabletype_id logproces_entity_id state_entity_idaggr logproces_entity_idymd state_semantic_idmut vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar vcd_value_flt vcd_value_txt _errval) KSADSEM.&state_id._VALUES&val_semdbnk. (keep=state_type_id state_entity_id state_entity_idvrs state_type_idymd state_digestion_idmut logproces_tabletype_id logproces_entity_id state_entity_idaggr logproces_entity_idymd state_semantic_idmut vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_value_flt vcd_value_txt ) KSADSEM.&state_id._NVT_VALUES&val_semdbnk. (keep=state_type_id state_entity_id state_entity_idvrs state_type_idymd state_digestion_idmut logproces_tabletype_id logproces_entity_id state_entity_idaggr logproces_entity_idymd state_semantic_idmut vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_value_flt vcd_value_txt ) ; length /* define length and types of variables */ state_type_id state_entity_id $4 state_entity_idvrs $2 state_type_idymd $8 state_digestion_idmut 8 logproces_tabletype_id $32 logproces_entity_id $4 state_entity_idaggr $16 logproces_entity_idymd $8 state_semantic_idmut 8 vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar $8 vcd_value_flt 8 vcd_value_txt $1024 ; %if (%length(&val_subsel) = 0 ) %then %do; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Combine all legacy datasets and the more recent versons Attenion: the indication ":" at the end does a search for all datasets having a name that is a match - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ Set KSADSTG.&state_id._STG_VALUES_LEGF_: KSADSTG.&state_id._STG_VALUES_SASF_: ; %end; %else %do; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Combine all named datasets for any version named after that "VALUES" &val_subsel is a list with all those names - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ %let selcnt = 1 ; %let val_subsel_&selcnt.=%scan(&val_subsel,&selcnt); Set KSADSTG.&state_id._STG_VALUES&&val_subsel_&selcnt. %let selcnt=%eval(&selcnt + 1) ; %let val_subsel_&selcnt.=%scan(&val_subsel,&selcnt); %do %while (%length(&&val_subsel_&selcnt.) > 0 & &selcnt < 100 ) ; KSADSTG.&state_id._STG_VALUES&&val_subsel_&selcnt. %let selcnt=%eval(&selcnt + 1) ; %let val_subsel_&selcnt.=%scan(&val_subsel,&selcnt); %end; ; %end; state_semantic_idmut = &state_semantic_idmut. ; format state_semantic_idmut datetime. ; _errval=0; Select ; when ( vcd_spec1_idvar =: "xstate" ) logproces_tabletype_id = "%lowcase(&state_id.)_monitor" ; ..... when ( vcd_spec1_idvar =: "......" ) logproces_tabletype_id = "....." ; otherwise do; /* Alle elements should go to some databank table. */ _errval=1 ; output KSADSEM.&state_id._Err_VALUES&val_semdbnk. ; end; end; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Leid uit de periode aanduiding de werkelijke periode als value af. Dogmatisch is een vertaaltabel voorgeschreven. Het werken met een paar coderegels is eenvoudiger in realisatie. Een optimale verwerking is via "select when" met gebruik van de intnx functie (tijdreizen). Onderhoud: Indien tijdsaanduidingen aangepast worden, dan moet deze code ook mee. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ tmp_date=intnx('YEAR',input(state_type_idymd,yymmdd8.),0,"b") ; /* begin van het jaar */ logproces_entity_idymd = " " ; Select ; when ( index(vcd_attrib_idvar ,"T00" ) > 0 ) logproces_entity_idymd = state_type_idymd ; when ( index(vcd_attrib_idvar ,"TY0" ) > 0 ) logproces_entity_idymd = state_type_idymd ; when ( index(vcd_attrib_idvar ,"TY1" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,-1,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"TY2" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,-2,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"TY3" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,-3,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"TY4" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,-4,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"TF1" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,+1,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"my1" ) > 0 ) logproces_entity_idymd = put(intnx('YEAR',tmp_date,-1,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m01" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,0,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m02" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,1,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m03" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,2,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m04" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,3,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m05" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,4,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m06" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,5,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m07" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,6,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m08" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,7,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m09" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,8,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m10" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,9,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m11" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,10,"e"), yymmddn8.) ; when ( index(vcd_attrib_idvar ,"m12" ) > 0 ) logproces_entity_idymd = put(intnx('MONTH',tmp_date,11,"e"), yymmddn8.) ; when ( vcd_attrib_idvar = " " ) ; otherwise do; /* er mag geen restklasse zijn. De code zal hierop een fout geven als dat wel gebeurt */ _errval=2; output KSADSEM.&state_id._Err_VALUES&val_semdbnk. ; end; end; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The entity in the staging is under what id it has been delivered. This identification can change in time. Delivering should be done with historical and current values. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ length state_validfrom_idymd state_validthru_idymd state_deliverythru_idymd state_deliveryfrom_idymd $8 ; logproces_entity_id=""; state_entity_idaggr = ""; state_validthru_idymd=""; state_validfrom_idymd=""; state_deliverythru_idymd=""; state_deliveryfrom_idymd=""; Set ksa_nrm.ksn_entity_&state_id. ( keep=state_entity_id state_entity_idmomenteel state_entity_idaggr state_validthru_idymd state_validfrom_idymd rename=(state_entity_idmomenteel=logproces_entity_id ) ) key=state_entity_id / unique ; if (_IORC_ ne 0) then do; _error_=0; _errval=3; output KSADSEM.&state_id._Err_LKUPentity&val_semdbnk. ; end; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When the period has been determined some elements could be in staging that are in reality not applicable, causing unnecessary noise. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ Select ; when ( logproces_tabletype_id in( ".....","...s" ) ) do ; Select ; /* correction toe delete values that are not valid wiht this entity for listed tabletypes */ when ( state_validthru_idymd = "") ; when ( logproces_entity_idymd > state_validthru_idymd ) do ; output KSADSEM.&state_id._NVT_VALUES&val_semdbnk. ; delete; end; otherwise ; end; end; when ( logproces_tabletype_id in( "xxxx","zxxxx" ) ) do ; Select ; when ( vcd_attrib_idvar = " " ) ; when ( state_validthru_idymd = " " & logproces_entity_idymd > state_validfrom_idymd ) ; when ( logproces_entity_idymd < state_deliveryfrom_idymd ) do ; output KSADSEM.&state_id._NVT_VALUES&val_semdbnk. ; delete; end; when ( state_deliverythru_idymd = " " ) ; when ( logproces_entity_idymd > state_deliverythru_idymd ) do ; output KSADSEM.&state_id._NVT_VALUES&val_semdbnk. ; delete; end; otherwise ; end; end; otherwise ; end; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Validate element are in valid within private metadata - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ Set ksa_nrm.KSN_VCD_SPEC&val_vcdspec. ( keep=vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar vcd_units_flt ) key=vcd_spec_idvar / unique ; if (_IORC_ ne 0) then do; _error_=0; output KSADSEM.&state_id._Err_LKUPVCD&val_semdbnk. ; end; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - vcd_spec3_idvar is possible empty, this should interpreted as missing. Attention for counting values it is in the next phase (2) prettier to have a dot "." when counting all values. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ if ( vcd_spec3_idvar = " " ) THEN vcd_spec3_idvar = "." ; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When vcd_units_flt is not having value 1. It is an indication from private metadata to do unit correections (vcd_units_txt) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ if ( vcd_units_flt not in (.,1) ) THEN do; vcd_value_flt=vcd_value_flt * vcd_units_flt ; END; if( _errval = 0 ) then output KSADSEM.&state_id._VALUES&val_semdbnk. ; run; %put SEMD NOTE: Monitor events in dataset: KSB_MON.&state_id._SEMDBNK_&val_semdbnk. %sysfunc(datetime(),datetime.) ; Proc sql noprint ; Select count(*) into : count_semdbnk_VALUES from KSADSEM.&state_id._VALUES&val_semdbnk. ; Select count(*) into : count_semdbnk_nvtVALUES from KSADSEM.&state_id._NVT_VALUES&val_semdbnk. ; Select count(*) into : count_semdbnk_errVALUES from KSADSEM.&state_id._Err_VALUES&val_semdbnk. ; Select count(*) into : count_semdbnk_errlkupentity from KSADSEM.&state_id._Err_LKUPentity&val_semdbnk. ; Select count(*) into : count_semdbnk_errlkupvcd from KSADSEM.&state_id._Err_LKUPVCD&val_semdbnk. ; quit; Data MON_&state_id._SEMDBNK_&val_semdbnk. ; file print; length state_id $4 user_id $8 val_semdbnk $16 ; format state_semantic_idmut datetime. ; state_id="&state_id." ; val_semdbnk ="&val_semdbnk." ; user_id="&sysuserid"; state_semantic_idmut = &state_semantic_idmut. ; count_semdbnk_VALUES = &count_semdbnk_VALUES ; count_semdbnk_nvtVALUES = &count_semdbnk_nvtVALUES ; count_semdbnk_errVALUES = &count_semdbnk_errVALUES ; count_semdbnk_errlkupentity = &count_semdbnk_errlkupentity ; count_semdbnk_errlkupvcd = &count_semdbnk_errlkupvcd ; put @ 1 "Process semantic dataset state_id. &val_semdbnk. " / @15 "count_semdbnk_VALUES " count_semdbnk_VALUES / @15 "count_semdbnk_nvtVALUES " count_semdbnk_nvtVALUES / @15 "count_semdbnk_errVALUES " count_semdbnk_errVALUES / @15 "count_semdbnk_errlkupentity " count_semdbnk_errlkupentity / @15 "count_semdbnk_errlkupvcd " count_semdbnk_errlkupvcd / ; run; Data KSB_MON.&state_id._SEMDBNK_&val_semdbnk. ; SET MON_&state_id._SEMDBNK_&val_semdbnk. %if (%sysfunc(exist(KSB_MON.&state_id._SEMDBNK_&val_semdbnk.) ) ) %then %do; KSB_MON.&state_id._SEMDBNK_&val_semdbnk. %end; %else %do; %if (%sysfunc(exist(KSA_MON.&state_id._SEMDBNK_&val_semdbnk.) ) ) %then %do; KSA_MON.&state_id._SEMDBNK_&val_semdbnk. %end; %end; ; run; %mend;