/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Macro's usage Note: When having defined a template for the information that is going to be processed. Make a macro call to ydatasheet_check for verification of all elements are valid elements. --> ydatasheet_check (state_type_id=...., state_type_idymd=....) ; When having verified and some differences are intended ones being new elements, (not zero differences) then the private metadata has to be updated. Make a macro call to ydatasheet_check for verification of all elements are valid elements. --> ydatasheet_correct (state_type_id=...., state_type_idymd=.... ) ; Notes: - do not use a ; (semicolon) after the macro-call when the ; would become a syntax problem. - KSADXRQ is the location of validated datasheet templates - KSB_MON 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. - The table key lookup is not well known code using SAS. It requires a defined index. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ /* * * ykwdrnt4 ydatasheet_check * * * */ /* * * Verify private metadata template datasheet is consistent * * * */ %macro ydatasheet_check (opt=, state_type_id=, state_type_idymd=, val_subnrm= ) ; %global xls_datasheet_cc ; %let xls_datasheet_cc =0 ; %local state_id state_type_idymd ; %global state_datasheet_idmut ; %let state_datasheet_idmut=%sysfunc(datetime()); %let state_type_id=%upcase(&state_type_id); Data KSADXRQ.XSLX_&state_type_id._&state_type_idymd._datasheet (keep=vcd_spec_idvar vcd_value_flt vcd_value_txt vcd_dscription_txt vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar) KSADXRQ.XSLX_&state_type_id._&state_type_idymd._NRMMISS (keep=vcd_spec_idvar vcd_value_flt vcd_value_txt vcd_dscription_txt vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar vcd_dsnorder_flt vcd_datatype_txt vcd_format_txt vcd_units_flt vcd_units_txt vcd_validfrom_idymd vcd_validthru_idymd ) ; length vcd_spec_idvar $34 vcd_value_flt $64 vcd_value_txt $255 vcd_dscription_txt $256 vcd_spec1_idvar vcd_spec2_idvar vcd_spec3_idvar vcd_attrib_idvar $8 ; set KSADXRQ.XSLX_&state_type_id._&state_type_idymd._datasheet (KEEP=vcd_spec_idvar vcd_value_flt vcd_value_txt vcd_dscription_txt) ; /* split de lookup variabele in vieren */ vcd_spec1_idvar = scan(vcd_spec_idvar,1,"_",'M') ; vcd_spec2_idvar = scan(vcd_spec_idvar,2,"_",'M') ; vcd_spec3_idvar = scan(vcd_spec_idvar,3,"_",'M') ; vcd_attrib_idvar = scan(vcd_spec_idvar,4,"_",'M') ; Set KSA_NRM.ksn_vcd_spec&val_subnrm. key=vcd_spec_idvar / unique ; if (_IORC_ ne 0) then do; _error_ = 0 ; vcd_dsnorder_flt =. ; vcd_datatype_txt =""; vcd_format_txt =""; vcd_units_flt =. ; vcd_units_txt =""; vcd_validfrom_idymd = ""; vcd_validthru_idymd = ""; output KSADXRQ.XSLX_&state_type_id._&state_type_idymd._NRMMISS ; end; output KSADXRQ.XSLX_&state_type_id._&state_type_idymd._datasheet ; run; Proc sql noprint ; Select count(*) into : count_datasheet_all from KSADXRQ.XSLX_&state_type_id._&state_type_idymd._datasheet ; Select count(*) into : count_datasheet_mis from KSADXRQ.XSLX_&state_type_id._&state_type_idymd._NRMMISS ; quit; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Keep a log of who has run this program and the results of the verifying - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ Data &state_id._datasheet_&state_type_idyr. ; length state_type_id $4 state_type_idymd $8 ccode $4 action user_id $8 ; format state_datasheet_idmut datetime. ; state_type_id="&state_type_id."; state_type_idymd = "&state_type_idymd" ; user_id="&sysuserid."; ccode = "&xls_datasheet_cc." ; action = "checknrm" ; state_datasheet_idmut="&state_datasheet_idmut" ; count_datasheet_all = &count_datasheet_all. ; count_datasheet_mis = &count_datasheet_mis. ; count_ksnvcd_old = . ; count_ksnvcd_new = . ; run; Data KSB_MON.&state_id._datasheet_&state_type_idyr. ; SET &state_id._datasheet_&state_type_idyr. %if (%sysfunc(exist(KSB_MON.&state_id._datasheet_&state_type_idyr.) ) ) %then %do; KSB_MON.&state_id._datasheet_&state_type_idyr. %end; %else %do; %if (%sysfunc(exist(KSA_MON.&state_id._datasheet_&state_type_idyr.) ) ) %then %do; KSA_MON.&state_id._datasheet_&state_type_idyr. %end; %end; ; run; %mend; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ /* * * ykwdrnt4 ydatasheet_correct * * * */ /* * * Coorect private metadata using template datasheet adding new elements * * * */ %macro ydatasheet_correct (opt=, state_type_id=, state_type_idymd=, val_subnrm= ) ; %global xls_datasheet_cc ; %let xls_datasheet_cc =0 ; %local state_id state_type_idymd ; %global state_datasheet_idmut ; %let state_datasheet_idmut=%sysfunc(datetime()); %let state_type_id=%upcase(&state_type_id); Proc sql noprint ; Select count(*) into : count_datasheet_mis from KSADXRQ.XSLX_&state_type_id._&state_type_idymd._NRMMISS ; Select count(*) into : count_ksnvcd_old from KSA_NRM.ksn_vcd_spec&val_subnrm. ; quit; %put %sysfunc(datetime(),datetime.) full join update doet tevens toevoegingen ; PROC SQL; CREATE TABLE WORK.ksn_vcd_spec&val_subnrm. AS SELECT coalescec(t1.vcd_spec1_idvar , t2.vcd_spec1_idvar ) as vcd_spec1_idvar length=8 , coalescec(t1.vcd_spec2_idvar , t2.vcd_spec2_idvar ) as vcd_spec2_idvar length=8 , coalescec(t1.vcd_spec3_idvar , t2.vcd_spec3_idvar ) as vcd_spec3_idvar length=8 , coalescec(t1.vcd_attrib_idvar , t2.vcd_attrib_idvar ) as vcd_attrib_idvar length=8 , coalesce (t1.vcd_dsnorder_flt , t2.vcd_dsnorder_flt ) as vcd_dsnorder_flt , coalescec(t1.vcd_datatype_txt , t2.vcd_datatype_txt ) as vcd_datatype_txt length=2 , coalescec(t1.vcd_format_txt , t2.vcd_format_txt ) as vcd_format_txt length=14 , coalescec(t1.vcd_units_txt , t2.vcd_units_txt ) as vcd_units_txt length=32 , coalesce (t1.vcd_units_flt , t2.vcd_units_flt ) as vcd_units_flt , coalescec(t1.vcd_validfrom_idymd , t2.vcd_validfrom_idymd ) as vcd_validfrom_idymd length=8 , coalescec(t1.vcd_validthru_idymd , t2.vcd_validthru_idymd ) as vcd_validthru_idymd length=8 , coalescec(t1.vcd_description_txt , t2.vcd_description_txt ) as vcd_description_txt length=256 FROM KSADXRQ.XSLX_&state_type_id._&state_type_idymd._NRMMISS t1 full join KSA_NRM.KSN_VCD_SPEC&val_subnrm. t2 on (t1.vcd_spec1_idvar = t2.vcd_spec1_idvar AND t1.vcd_spec2_idvar = t2.vcd_spec2_idvar AND t1.vcd_spec3_idvar = t2.vcd_spec3_idvar AND t1.vcd_attrib_idvar = t2.vcd_attrib_idvar) ORDER BY vcd_spec1_idvar, vcd_spec2_idvar, vcd_spec3_idvar, vcd_attrib_idvar; CREATE UNIQUE INDEX vcd_spec_idvar ON WORK.ksn_vcd_spec&val_subnrm. (vcd_spec1_idvar,vcd_spec2_idvar,vcd_spec3_idvar,vcd_attrib_idvar); QUIT; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Sorting is done to be able to see more logic in the private data. Using it a a SAS dataset sorted and index on the dataset Will perform reliable in skip sequential search. SAS is not optimised for transactional systems but for analytics. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ %put %sysfunc(datetime(),datetime.) kopieer data over origineel ; proc copy in=WORK out=KSA_NRM ; select ksn_vcd_spec&val_subnrm. ; run; %put %sysfunc(datetime(),datetime.) A deduplicated version (removed vcd_attrib_idvar) ; %yvcd_specper (val_subnrm=&val_subnrm.); %put %sysfunc(datetime(),datetime.) Deduplicate version of vdc private metdata finished ; Proc sql noprint ; Select count(*) into : count_ksnvcd_new from KSA_NRM.ksn_vcd_spec&val_subnrm. ; quit; /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Keep a log of who has run this program and the results of adding elements - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ Data &state_id._datasheet_&state_type_idjr. ; length state_type_id $4 state_type_idymd $8 ccode $4 action user_id $8 ; format state_datasheet_idmut datetime. ; state_type_id="&state_type_id."; state_type_idymd = "&state_type_idymd" ; user_id="&sysuserid."; ccode = "&xls_datasheet_cc." ; action = "corrects" ; state_datasheet_idmut="&state_datasheet_idmut." ; count_datasheet_all = . ; count_datasheet_mis = &count_datasheet_mis. ; count_ksnvcd_old = &count_ksnvcd_old. ; count_ksnvcd_new = &count_ksnvcd_new. ; run; Data KSB_MON.&state_id._datasheet_&state_type_idjr. ; SET &state_id._datasheet_&state_type_idjr. %if (%sysfunc(exist(KSB_MON.&state_id._datasheet_&state_type_idjr.) ) ) %then %do; KSB_MON.&state_id._datasheet_&state_type_idjr. %end; %else %do; %if (%sysfunc(exist(KSA_MON.&state_id._datasheet_&state_type_idjr.) ) ) %then %do; KSA_MON.&state_id._datasheet_&state_type_idjr. %end; %end; ; run; %mend;