/*___________________________________________________________________ Ĥ MACRO: CONTENTS Ĥ Ĥ JOB: Data Quality Ĥ Ĥ PROGRAMMER: Mahmoud Azimaee Ĥ Ĥ DATE: April 2011 Ĥ Ĥ DESCRIPTION: This Macro runs PROC CONTENTS for a series of Ĥ Ĥ tables within a specified Domain and Database and Ĥ Ĥ generates Overview Table as described in Data Ĥ Ĥ Quality Report. Ĥ Ĥ PARAMETERS: DOMAIN= Database domain on SPDS Ĥ Ĥ DB= Database prefix Ĥ Ĥ EXAMPLE: %CONTENTS(HEALTH,BMD); Ĥ ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ*/ **********************************************************************; %MACRO CONTENTS (DOMAIN,DB); %PUT ___________________________________________________________________; %PUT Ĥ Manitoba Centre for Health policy (MCHP) Ĥ; %PUT Ĥ MACRO: CONTENTS Ĥ; %PUT Ĥ JOB: Data Quality Ĥ; %PUT Ĥ PROGRAMMER: Mahmoud Azimaee Ĥ; %PUT Ĥ DATE: April 2011 Ĥ; %PUT Ĥ Running for: &DOMAIN..&DB ... ; %PUT ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ; %LET DOMAIN=%UPCASE(&DOMAIN); %LET DB=%UPCASE(&DB); PROC SQL NOPRINT; SELECT MEMNAME INTO :DS SEPARATED BY " " FROM DICTIONARY.members WHERE LIBNAME = "&DOMAIN" AND SUBSTR(MEMNAME,1,%SYSFUNC(LENGTH(&DB)))="&DB"; quit; %LET DSN=%EVAL(%SYSFUNC(COUNTC(&DS,' '))+1); %DO I=1 %TO &DSN; PROC CONTENTS DATA=&DOMAIN..%SCAN(&DS,&I," ") OUT=contents&I NOPRINT; run; data contents_&DB; %IF &I=1 %THEN %DO; set contents&I; %END; %ELSE %DO; set contents_&DB contents&I; %END; run; %END; PROC DATASETS; delete contents1-contents&I; run; PROC SORT DATA=contents_&DB; by MEMNAME; run; Data overview_&DB; set contents_&DB; by MEMNAME; if first.MEMNAME then nvar=0;; nvar+1; if last.MEMNAME; keep LIBNAME MEMNAME MEMLABEL NOBS NVAR; Label NVAR='# of Variables'; run; PROC PRINT DATA=overview_&DB LABEL; TITLE "Overview for &DOMAIN..&DB Database"; run;; PROC EXPORT DATA= WORK.overview_&DB OUTFILE= "/dq/saswork/&spdsuser/&DOMAIN._&DB._OVERVIEW.xls" /*where /dq/saswork/&spdsuser is the location to output overview table*/ DBMS=XLS REPLACE; RUN; %MEND CONTENTS;