**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: utility_macros.sas * Created (mm/dd/yyyy): 12/20/2020 * *-------------------------------------------------------------------------------------------------- * PURPOSE: This program includes the following macros: * - %isdata() macro determines whether a dataset is empty or not * - %create_comma_charlist() macro converts space delimited list to comma delimited list with quotes * - %alphabetizevarutil() macro alphabetizes variables in a data step * - %tableletter() macro increments a letter suffix * - %varexist() macro checks for the existence of a variable * - %convert_categories() macro converts categories to mathematical expression * - %output_datasets() macro output SAS datasets * - %nonrep() macro removes repeated words in macro variable * - %varlength macro gets a variable length and type * * Program inputs: * - * * Program outputs: * - * * PARAMETERS: * * Programming Notes: * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Sentinel Coordinating Center * info@sentinelsystem.org * ***************************************************************************************************; *Macro to determine whether a dataset is empty or not; %MACRO ISDATA(dataset=); %GLOBAL NOBS; %let NOBS=0; %if %sysfunc(exist(&dataset.))=1 and %LENGTH(&dataset.) ne 0 %then %do; data _null_; dsid=open("&dataset."); call symputx("NOBS",attrn(dsid,"NLOBS")); run; %end; %PUT &NOBS.; %MEND ISDATA; *Macro for converting macro variable with space deliminated list to comma deliminated list with quotation around each word; %macro create_comma_charlist(inlist=, outlist=); %global &outlist.; %let countvars = %sysfunc(Countw(%quote(&inlist.), ' ')); %do c = 1 %to &countvars.; %let word = %scan(%quote(&inlist),&c., ' '); %if &c. = 1 %then %do; %let &outlist. = "&word."; %end; %else %do; %let &outlist. = &&&outlist. , "&word."; %end; %end; %let &outlist = %upcase(&&&outlist); %mend create_comma_charlist; *Macro to alphabetize variables in a data step; %macro alphabetizevarutil(array=, in=, out=); array &array.[10] $50 _temporary_; call missing(of &array.[*]); do i = 1 to dim(&array.) until(p eq 0); call scan(&in.,i,p,l); &array.[i] = substrn(&in.,p,l); end; call sortc(of &array.[*]); length &out. $100; &out. = catx(' ',of &array.[*]); drop i p l &in.; %mend; *Macro for incrementing table/figure letter suffix; %macro tableletter(); %if %eval(&tablecount = 0) %then %do; %let tableletter = ; %end; %else %do; %if %eval(%sysfunc(mod(&tablecount.,26))=0) %then %do; %let div = %eval((&tablecount. / 26)-1); %put &div.; %let secondplace = %eval(&tablecount - (26*&div));%put &secondplace.; %end; %else %do; %let div = %sysfunc(int(&tablecount. / 26)); %put &div.; %let secondplace = %eval(&tablecount - (26*&div));%put &secondplace.; %end; %if %eval(&div = 0) %then %do; %let tableletter = %scan(a b c d e f g h i j k l m n o p q r s t u v w x y z, &tablecount.); %end; %else %do; %let tableletter = %scan(a b c d e f g h i j k l m n o p q r s t u v w x y z, &div.)%scan(a b c d e f g h i j k l m n o p q r s t u v w x y z, &secondplace); %end; %end; %put tableletter = &tableletter.; %let tablecount = %eval(&tablecount + 1); /*+1 to counter*/ %mend; *Macro to check the existence of a variable; %macro varexist (ds,var); %local dsid rc ; %let dsid = %sysfunc(open(&ds)); %if (&dsid) %then %do; %if %sysfunc(varnum(&dsid,&var)) %then 1; %else 0 ; %let rc = %sysfunc(close(&dsid)); %end; %else 0; %mend varexist; /*********************************************************************************************/ /* Define macro variables for superscripts that correspond to required footnotes */ /*********************************************************************************************/ %macro assign_superscripts(type =, order =); %global super_&type.; %let super_&type. =; %isdata(dataset=_footnotes); %if %eval(&nobs.>0) and "&order" ne "" %then %do; proc sql noprint; select cat('^{Super ',footnote_order,'}') into: super_&type. separated by '^{Super ,}' from _footnotes where order in (&order.); quit; %end; %mend assign_superscripts; *Macro for converting categories to mathematical expression; %macro convert_categories(var=, categories=); %global num_categories categories_boolean ; %let num_categories = ; %let categories_boolean = ; %let num_categories = %sysfunc(countw(&categories, ' ')); %do loop = 1 %to &num_categories; /*variable list*/ /*mathematical expressions*/ %let cat = %scan(&categories., &loop., ' '); %put &cat.; /*no range of values (i.e. 0, <10, <=20, >60 >=75, 65+)*/ %if %index(&cat., -) = 0 %then %do; %if %index(&cat.,+) > 0 %then %do; %let cat = &var>=%sysfunc(tranwrd(&cat., +, )); %end; %else %if %index(&cat.,<) > 0 or %index(&cat.,=) > 0 or %index(&cat.,>) > 0 %then %do; %let cat = &var.&cat.; %end; %else %do; %let cat = &var.=&cat.; %end; %end; /*range of values inclusive of boundary (i.e. 25-50)*/ %else %if %index(&cat., -) > 0 & %index(&cat., <) = 0 & %index(&cat., >) = 0 %then %do; %let cat = %sysfunc(tranwrd(&cat., -, <=&var.<=)); %end; /*range of values inclusive of upper boundary, (i.e. 25<-60)*/ %else %if %index(&cat., -) > 0 & %index(%scan(&cat., 1, '-'), <) > 0 %then %do; %let cat = %scan(&cat., 1, '-')&var.<=%scan(&cat., 2, '-'); %end; /*range of values inclusive of lower boundary, (i.e. 50-<75)*/ %else %if %index(&cat., -) > 0 & %index(%scan(&cat., 2, '-'), <) > 0 %then %do; %let cat = %scan(&cat., 1, '-')<=&var.%scan(&cat., 2, '-'); %end; %let categories_boolean = &categories_boolean. &cat.; %end; %put &categories; %put &categories_boolean.; %mend convert_categories; %macro output_datasets (dataset=, inlib=work, outlib=, name=&infile.); %if &output_agg_data. = Y and &leavebehindreport = N %then %do; proc datasets library = &inlib; copy out=&outlib. memtype=data; select &dataset.(memtype=data) ; quit; proc datasets library = &outlib.; change &dataset. = agg_&name.; quit; %end; %mend output_datasets; *Macro to remove repeated words in a macro variable; %macro nonrep(invar= , outvar= ); %global &outvar; %let long = ; %if %str(&&&invar) ne %str() %then %do; %do w=1 %to %sysfunc(countw(&&&invar)); %if %sysfunc(indexw(&long, %scan(&&&invar,&w))) = 0 %then %do; %let long = &long %scan(&&&invar,&w); %end; %end; %end; %let &outvar = &long.; %mend; *Macro to get variable length and type; %macro varlength (var = , indata = ); %global &var._len &var._typ; proc contents noprint data=&indata. out=var_length_contents; run; proc sql noprint; select length ,type into: &var._len trimmed ,:&var._typ from var_length_contents where lowcase(name) = "%lowcase(&var.)"; quit; proc datasets noprint lib = work; delete var_length_contents; quit; %mend varlength;