/*------------------------------------------------------------------------- © Copyright 2011-2013 University of Manitoba This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see . ----------------------------------------------------------------------------- Author: Say Hong Please direct questions and comments to info@cpe.umanitoba.ca Mar. 20, 2013. This macro has been developed as an automated process for generating data quality tables and charts. Running this macro will automatically open an unformatted excel output, and import Excel VBA code (dq_automate.bas) and run it to produce tables and charts without manual intervention. Parameters: ds = Name of input dataset (same value as the VIMO macro) period = label the time period in the excel output dir = directory where Excel is output wrkbook = name of the Excel output memnum = List of cluster members that are used to produce VIMO table, if blank then the macro will be run for a specific dataset (non-cluster) or the whole cluster if the dataset is a cluster (same value as the VIMO macro) save = specify whether to save the vimo table and chart (valid value is Y/N). Default value is N rnglen = this parameter allows the user to split the huge vimo table that contains hundred of variables into mulitple smaller vimo tables, rnglen is the number of variables that each smallter vimo table contains. For example, if a vimo table contains 150 variables and the user would like to split the table into 3 smaller tables each contains 50 variables, then set rnglen=50. ---------------------------------------------------------------------------*/ proc sql outobs=1 nowarn noprint; select xpath into :vbapath from dictionary.extfiles where fileref eqt '#LN' order by fileref descending; quit; %let vbapath = %substr(&vbapath, 1, %index(&vbapath, %scan(&vbapath, -1, \))-2); %macro DQ_GEN(ds=, period=, Dir=, wrkbook=, memnum=, save=N, rnglen=); %put; %put NOTE: DQ Automation; %put; %let label =; %let nobs =; %let gtitle=; %if %substr(&dir, %length(&dir), 1) = \ %then %let dir = %substr(&dir, 1, %length(&dir) - 1); %if &ds ^= %str() %then %do; ods select attributes; ods output attributes=attr; proc contents data=&ds; run; ods output close; ods select all; proc sql noprint; select cvalue1 into :label from attr where label1='Label'; %if &memnum = %then %do; select cvalue2 into :nobs from attr where label2='Observations'; %end; quit; %if &memnum ^= %then %do; %let memn = %sysfunc(countw(&memnum)); %do i = 1 %to &memn; %let mem = %scan(&memnum, &i); data data&i; set &ds(memnum=&mem); run; %end; data _tmp; set data1-data&memn; run; %let dsid = %sysfunc(open(_tmp)); %let nobs = %sysfunc(attrn(&dsid,nlobs)); %let rc = %sysfunc(close(&dsid)); proc datasets nolist memtype=data lib=work; delete data1-data&memn _tmp; quit; %end; proc datasets nolist memtype=data; delete attr; quit; %if %index(&ds, .) ^= 0 %then %do; %let gtitle = %scan(&ds, 2, .); %let ds = %scan(&ds, 2, .); %if &memnum ^= %then %let ds = &ds. (cluster members = &memnum); %end; %else %let gtitle = &ds; %end; data _null_; file %unquote(%nrbquote('&Dir\import.vbs')); put 'set xlobj = createobject("excel.application")'; put 'xlobj.visible = True'; put 'xlobj.displayalerts = False'; put %unquote(%nrbquote('xlobj.workbooks.open("&Dir\&wrkbook..xls")')); put %unquote(%nrbquote('xlobj.application.vbe.activevbproject.vbcomponents.import "&vbapath.\dq_automate.bas"')); put 'For Each wrksheet In xlobj.Worksheets'; put 'wrksheet.activate'; put 'If wrksheet.Name = "vimo" Then'; put 'flag = True'; put 'Exit For'; put 'End If'; put 'Next'; put 'If flag = True Then'; put 'mxrow=xlobj.sheets("vimo").UsedRange.Rows.Count'; put 'End If'; %if &rnglen ^= %then %do; put "For x = 2 to mxrow step &rnglen"; put 'if x = 2 then'; put 'i = 1'; put 'else'; put "i = i + 1"; put 'end if'; put 'if x = 2 then'; put 'xlobj.worksheets.add, xlobj.worksheets("vimo")'; put 'else'; put 'xlobj.worksheets.add, xlobj.worksheets("vimo"&i-1)'; /*add sheets after vimo(i-1)*/ put 'end if'; put 'xlobj.activesheet.name = "vimo"&i'; put 'xlobj.sheets("vimo"&i).select'; put 'xlobj.range("A1").select'; put %unquote(%nrbquote('xlobj.sheets("vimo").range("A1:M1").copy')); put 'xlobj.activesheet.pastespecial'; put %unquote(%nrbquote('record = &rnglen - 1')); put 'xlobj.sheets("vimo").range("A"&x&":M"&x + record).copy'; put 'xlobj.range("A2").select'; put 'xlobj.activesheet.pastespecial'; put 'xlobj.range("A1").select'; put 'next'; %end; put 'For Each wrksheet In xlobj.Worksheets'; put 'wrksheet.activate'; put 'If Mid(wrksheet.Name, 1, 4) = "vimo" Then'; put 'xlobj.run("gen_report")'; put %unquote(%nrbquote('xlobj.Range("B1").Value = "Dataset Label: &label"')); put 'xlobj.Range("B1").Font.Bold = True'; put %unquote(%nrbquote('xlobj.Range("B2").Value = "Dataset Name: &ds"')); put 'xlobj.Range("B2").Font.Bold = True'; put %unquote(%nrbquote('xlobj.Range("E1").Value = "Records: &nobs"')); put 'xlobj.Range("E1").Font.Bold=True'; put %unquote(%nrbquote('xlobj.Range("E2").Value = "Period: &period"')); put 'xlobj.Range("E2").Font.Bold=True'; put 'With xlobj.Worksheets(wrksheet.Name).ChartObjects(1).Chart'; put %unquote(%nrbquote('.ChartTitle.Text = ">itle"')); put 'End With'; put 'Elseif wrksheet.Name = "agreement" Then'; /*put 'xlobj.Range("C1").Value = "Degree of Agreement with Registry - Date of Birth (Kappa Statistic)"';*/ put 'xlobj.run("other")'; put 'Elseif wrksheet.Name = "linkability_over_years" Then'; put 'xlobj.run("other")'; put 'xlobj.run("AddLineChart")'; put 'Else'; put 'xlobj.run("other")'; put 'End if'; put 'Next'; put 'xlobj.ActiveWorkbook.VBProject.VBComponents.Remove xlobj.ActiveWorkbook.VBProject.VBComponents("Module1")'; %if %upcase(&save) = Y %then put %unquote(%nrbquote('xlobj.activeworkbook.saveas "&Dir\&wrkbook..xls"'))%str(;); put 'set objFSO = CreateObject("Scripting.FileSystemObject")'; put 'strScript = Wscript.ScriptFullName'; put 'objFSO.DeleteFile(strScript)'; run; systask command %unquote(%nrbquote('"&Dir\import.vbs"')) wait; %mend DQ_GEN;