Do file to generate automated table of variables - help translating SAS code

[Jackie Reuben]

I work for the DC DOH, and I'm trying to create a do file to create tables to display healthcare-associated infections data. The Council for State and Territorial Epidemiologists (CSTE) has provided a suggested standardized reporting template, and provided SAS code to generate the tables (SAS code and example output table attached). I've been trying to translate this SAS code to STATA , but am stumped on how best to export these data into a similar table format. Should I use putexcel? Or create a matrix? The variables are string, and no tabulations or summary statistics are needed, but automating the creation of these tables would definitely save time.
************************************************** ************************************************** ***********
Data Analysis and Presentation Standardization Toolkit: Generating a Facility-specific SIR Summary Table

The code below creates the SIR Summary table, using the conventions shown in the toolkit.

Developed by:
Ashley Fell, MPH
HAI Epidemiologist
TN Dept of Health
August 2015

************************************************** ************************************************** ***********;

/*Read in sample CLABSI data*/
data report_CLABSI;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $24. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial Adult and Pediatric ICUs 1523 1 2.80 0.36 0.21 0.58 3
Clean Memorial Neonatal ICUs (NICUs) 803 0 1.80 0.00 . 0.87 3
Clean Memorial Wards 986 3 4.11 0.73 0.71 1.02 1
Town Surgical Hospital Adult and Pediatric ICUs 251 0 0.64 N/A N/A N/A 4
Vine Medical Center Adult and Pediatric ICUs 2961 3 3.21 0.93 0.76 1.98 1
Vine Medical Center Wards 1002 2 2.84 0.70 0.64 0.93 3
;
run;

data report_CLABSI; format CI $12. SIR_int $24.; set report_CLABSI;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/*Read in sample CAUTI data*/
data report_CAUTI;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $24. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial Adult and Pediatric ICUs 6798 6 4.33 1.39 1.31 1.97 2
Town Surgical Hospital Adult and Pediatric ICUs 598 3 2.10 1.43 0.89 2.89 1
Vine Medical Center Adult and Pediatric ICUs 5139 5 3.98 1.26 0.87 1.35 1
;
run;

data report_CAUTI; format CI $12. SIR_int $24.; set report_CAUTI;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/*Read in sample SSI COLO data*/
data report_COLO;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $22. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial Colon Surgery 54 0 1.70 0.00 . 1.45 1
Town Surgical Hospital Colon Surgery 265 2 2.70 0.74 0.51 0.82 3
Vine Medical Center Colon Surgery 161 4 3.60 1.11 0.73 1.27 1
;
run;

data report_COLO; format CI $12. SIR_int $24.; set report_COLO;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/*Read in sample SSI HYST data*/
data report_HYST;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $22. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial Abdominal Hysterectomy 78 5 3.00 1.67 1.44 3.98 2
Town Surgical Hospital Abdominal Hysterectomy 200 5 6.80 0.74 0.62 1.04 1
Vine Medical Center Abdominal Hysterectomy 107 6 5.61 1.07 0.63 1.47 1
;
run;

data report_HYST; format CI $12. SIR_int $24.; set report_HYST;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/*Read in sample CDI LabID data*/
data report_CDI;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $12. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial CDI 6700 3 6.52 0.46 0.41 0.97 3
Town Surgical Hospital CDI 1202 5 4.09 1.22 0.93 1.49 1
Vine Medical Center CDI 10209 7 5.11 1.37 1.02 1.59 2
;
run;

data report_CDI; format CI $12. SIR_int $24.; set report_CDI;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/*Read in sample MRSA LabID data*/
data report_MRSA;
**Variable formatting: edit as needed (e.g., facility name may require > 22 characters);
format ddays comma5. exp 4.2 sig 1.;
input FacName & $22. Unit & $12. ddays obs exp SIR & :$4. SIRL & :$4. SIRU & :$4. sig;

datalines;
Clean Memorial MRSA 6798 4 6.52 0.61 0.41 0.97 3
Town Surgical Hospital MRSA 1202 1 0.98 N/A N/A N/A 4
Vine Medical Center MRSA 10802 6 3.03 1.98 1.24 2.01 2
;
run;

data report_MRSA; format CI $12. SIR_int $24.; set report_MRSA;
if SIR='N/A' then CI='N/A';
else if SIR ne 'N/A' then CI='('||SIRL||', '||SIRU||')';

**Add SIR interpretation text which will appear next to the symbol;
if sig=1 then SIR_int="= Same";
else if sig=2 then SIR_int="#{unicode 2715} Worse"; /*Use unicode to create the symbols*/
else if sig=3 then SIR_int="#{unicode 2605} Better"; /*Use unicode to create the symbols*/
else if sig=4 then SIR_int="No Conclusion";

**Make exp a text variable and add "Less than 1.0" when predicted infections <1;
exp_temp=put(exp,3.1);
if exp<1.0 then exp1="Less than 1.0";
else if exp>=1.0 then exp1=input(exp_temp,$3.);
run;


/************************************************** **********************************************
* Combine the different infecion types into one data set *
************************************************** **********************************************/
proc sort data=report_CLABSI; by facname; run;
proc sort data=report_CAUTI; by facname; run;
proc sort data=report_COLO; by facname; run;
proc sort data=report_HYST; by facname; run;
proc sort data=report_CDI; by facname; run;
proc sort data=report_MRSA; by facname; run;

data report_CLABSI_a;
set report_CLABSI;
where unit="Adult and Pediatric ICUs";
run;

data report_CAUTI_a;
set report_CAUTI;
where unit="Adult and Pediatric ICUs";
run;

data report_summary; format SIR_int_clab SIR_int_cau SIR_int_colo SIR_int_hyst SIR_int_cdi SIR_int_mrsa $24.;
merge report_CLABSI_a (keep=FacName sig rename=sig=sig_clab)
report_CAUTI_a (keep=FacName sig rename=sig=sig_cau)
report_COLO (keep=FacName sig rename=sig=sig_colo)
report_HYST (keep=FacName sig rename=sig=sig_hyst)
report_CDI (keep=FacName sig rename=sig=sig_cdi)
report_MRSA (keep=FacName sig rename=sig=sig_mrsa);
by facname;

**Add SIR interpretation symbol;
if sig_clab=1 then SIR_int_clab="=";
else if sig_clab=2 then SIR_int_clab="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_clab=3 then SIR_int_clab="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_clab=4 then SIR_int_clab="No Conclusion";

if sig_cau=1 then SIR_int_cau="=";
else if sig_cau=2 then SIR_int_cau="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_cau=3 then SIR_int_cau="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_cau=4 then SIR_int_cau="No Conclusion";

if sig_colo=1 then SIR_int_colo="=";
else if sig_colo=2 then SIR_int_colo="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_colo=3 then SIR_int_colo="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_colo=4 then SIR_int_colo="No Conclusion";

if sig_hyst=1 then SIR_int_hyst="=";
else if sig_hyst=2 then SIR_int_hyst="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_hyst=3 then SIR_int_hyst="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_hyst=4 then SIR_int_hyst="No Conclusion";

if sig_cdi=1 then SIR_int_cdi="=";
else if sig_cdi=2 then SIR_int_cdi="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_cdi=3 then SIR_int_cdi="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_cdi=4 then SIR_int_cdi="No Conclusion";

if sig_mrsa=1 then SIR_int_mrsa="=";
else if sig_mrsa=2 then SIR_int_mrsa="#{unicode 2715}"; /*Use unicode to create the symbols*/
else if sig_mrsa=3 then SIR_int_mrsa="#{unicode 2605}"; /*Use unicode to create the symbols*/
else if sig_mrsa=4 then SIR_int_mrsa="No Conclusion";
run;







/************************************************** **********************************************
* *
* Summary Table *
* *
************************************************** **********************************************/
ods escapechar="#";

data legend;
input better & $200. same & $200. worse & $200. nocon & $200.;

datalines;
Fewer infections (#S={font_weight=bold}better#S={font_weight=medium }) than predicted based on the national experience.* About the #S={font_weight=bold}same#S={font_weight=medium} number of infections as predicted based on the national experience.* More infections (#S={font_weight=bold}worse#S={font_weight=medium} ) than predicted based on the national experience.* When the number of predicted infections is less than 1, no conclusion can be made.

;
run;

data legend; set legend;
bettersym="#{unicode 2605}";
samesym="=";
worsesym="#{unicode 2715}";
noconsym="No Conclusion";
run;


/*Create the legend as a macro so we can easily add it to each page*/
%MACRO LEGEND;
**Legend**;
title;
proc report data=legend spanrows missing nowd headskip headline split='\'
/*Set the color/text style elements for the table*/
style(header)={ background=lightgrey foreground=black font_face=georgia font_weight=bold font_size=11pt}
style(column)={ background=white foreground=black bordertopcolor=black font_face=georgia font_size=10pt};

/*Identify the variables that will appear/are referenced in the table, and create any headers that span more than one variable*/
column ("Legend" bettersym better samesym same worsesym worse noconsym nocon);

/*Create header labels for each variable, set column widths and alignment*/
define bettersym / display center "" style(column)=[width=.38in vjust=middle];
define better / display "" style(column)=[width=1.76in vjust=middle] ;
define samesym / display center "" style(column)=[width=.38in vjust=middle];
define same / display "" style(column)=[width=1.76in vjust=middle] ;
define worsesym / display center "" style(column)=[width=.38in vjust=middle];
define worse / display "" style(column)=[width=1.76in vjust=middle] ;
define noconsym / display center "" style(column)=[width=1in vjust=middle];
define nocon / display "" style(column)=[width=1.9in vjust=middle] ;

/*Use the appropriate font/color for symbols*/
compute bettersym;
call define(_col_,"style", "style=[font_size=16pt fontweight=bold color=green borderrightcolor=white]");
endcomp;
compute samesym;
call define(_col_,"style", "style=[font_size=30pt fontweight=bold color=gray borderrightcolor=white]");
endcomp;
compute worsesym;
call define(_col_,"style", "style=[font_size=20pt fontweight=bold color=red borderrightcolor=white]");
endcomp;
compute noconsym;
call define(_col_,"style", "style=[font_size=11pt fontweight=bold color=black borderrightcolor=white]");
endcomp;

/*Footnote with baseline years*/
compute after /style=[font_size=9pt font_style=italic color=black fontfamily='georgia'];
line "*National experience contains data from 2006 – 2008 for CLABSI and SSI, 2009 for CAUTI, and 2010-2011 for MRSA and C. difficile Laboratory-Identified Events.";
endcomp;
run;
%MEND LEGEND;


/*Create a RTF document to output the tables (can be opened in MS Word)*/
title1; title2;
options nodate nonumber PAPERSIZE= STANDARD ORIENTATION = LANDSCAPE
topmargin=".75in" leftmargin=".75in" rightmargin=".75in" bottommargin=".75in";
%let filename="C:\Users\dc49c16\Desktop\Figures\DAPS_SI R Summary Table.RTF";
ods rtf file=&filename startpage=never /*bodytitle*/;


/*Print the legend*/
%LEGEND;


/*Title for the Summary table*/
ods rtf text="#S={font_size=11pt font_face='georgia' font_weight=bold just=l}[STATE] Infections Compared to the National Experience, Acute Care Hospital Report, [TIME PERIOD]";

**Summary Table**;
proc report data=report_summary spanrows missing nowd headskip headline split='\'

/*Set the color/text style elements for the table*/
style(header)={ background=lightgrey foreground=black font_face=georgia font_weight=bold font_size=11pt}
style(column)={ background=white foreground=black bordertopcolor=black font_face=georgia font_size=11pt};

/*Identify the variables that will appear/are referenced in the table, and create any headers that span more than one variable*/
column FacName SIR_int_clab SIR_int_cau SIR_int_colo SIR_int_hyst SIR_int_cdi SIR_int_mrsa
sig_clab sig_cau sig_colo sig_hyst sig_cdi sig_mrsa;

/*Create header labels for each variable, set column widths and alignment*/
define FacName / order center "Facility Name" style(column)=[width=1.75in vjust=middle];
define SIR_int_clab / display center "Bloodstream Infections (CLABSI)**" style(column)=[width=1.15in vjust=middle] ;
define SIR_int_cau / display center "Urinary Tract Infections (CAUTI)**" style(column)=[width=1.0in vjust=middle] ;
define SIR_int_colo / display center "Surgical Site Infections from Colon Surgeries" style(column)=[width=1.15in vjust=middle] ;
define SIR_int_hyst / display center "Surgical Site Infections from Abdominal Hysterectomies" style(column)=[width=1.4in vjust=middle] ;
define SIR_int_cdi / display center "#S={font_style=italic}C. difficile #S={font_style=roman}Events#{unicode 2020}" style(column)=[width=1.0in vjust=middle] ;
define SIR_int_mrsa / display center "Methicillin-Resistant #S={font_style=italic}Staphylococcus aureus#S={font_style=roman} (MRSA) Events#{unicode 2020}" style(column)=[width=1.75in vjust=middle] ;
define sig_clab / display noprint;
define sig_cau / display noprint;
define sig_colo / display noprint;
define sig_hyst / display noprint;
define sig_cdi / display noprint;
define sig_mrsa / display noprint;


/*Use the appropriate font/color for SIR interpretation*/
compute sig_clab;
if sig_clab=1
then call define('SIR_int_clab',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_clab=2
then call define('SIR_int_clab',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_clab=3
then call define('SIR_int_clab',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_clab=4
then call define('SIR_int_clab',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

compute sig_cau;
if sig_cau="1"
then call define('SIR_int_cau',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_cau="2"
then call define('SIR_int_cau',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_cau="3"
then call define('SIR_int_cau',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_cau="4"
then call define('SIR_int_cau',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

compute sig_colo;
if sig_colo="1"
then call define('SIR_int_colo',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_colo="2"
then call define('SIR_int_colo',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_colo="3"
then call define('SIR_int_colo',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_colo="4"
then call define('SIR_int_colo',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

compute sig_hyst;
if sig_hyst="1"
then call define('SIR_int_hyst',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_hyst="2"
then call define('SIR_int_hyst',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_hyst="3"
then call define('SIR_int_hyst',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_hyst="4"
then call define('SIR_int_hyst',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

compute sig_cdi;
if sig_cdi="1"
then call define('SIR_int_cdi',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_cdi="2"
then call define('SIR_int_cdi',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_cdi="3"
then call define('SIR_int_cdi',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_cdi="4"
then call define('SIR_int_cdi',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

compute sig_mrsa;
if sig_mrsa="1"
then call define('SIR_int_mrsa',"style", "style=[font_size=26pt fontweight=bold color=gray]");
else if sig_mrsa="2"
then call define('SIR_int_mrsa',"style", "style=[font_size=16pt fontweight=bold color=red]");
else if sig_mrsa="3"
then call define('SIR_int_mrsa',"style", "style=[font_size=16pt fontweight=bold color=green]");
else if sig_mrsa="4"
then call define('SIR_int_mrsa',"style", "style=[font_size=12pt fontweight=bold color=black]");
endcomp;

/*Footnote*/
compute after /style=[font_size=10pt font_style=italic color=black fontfamily='georgia' just=l];
line "**CLABSI and CAUTI data include only adult and pediatric intensive care units (ICUs); data from neonatal ICUs (NICUs) and ward locations can be found in HAI-specific data tables.";
line "#{unicode 2020} These refer to hospital-onset laboratory-identified events. MRSA events include only those identified in the bloodstream.";
endcomp;
run;

ods rtf close;

[Mike Lacy]

Showing an example of the data from which you want to produce these results would help people help you. In this regard, click on the FAQ tab at the top of the screen, and search on "dataex", which a utility for posting example data. Also, many of us will not open docx files from strangers, so posting that in PDF also would be helpful.

[Jackie Reuben]

Thanks, will do.

[Jackie Reuben]

Here is a sample of the data:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 locationtype int summaryyr long centrallinedays byte events double numberpredicted str5 sir2 str14 ci str13 sig
"CC"       2016 48623 60 56.630031768 "1.06"  "(0.816, 1.354)" "Same"        
"CC_N"     2016 12688  9 17.986843553 ".5"    "(0.244, 0.918)" "Better"      
"OTHER"    2016   574  0   .811419088 "N/A"   "N/A"            "No Conclusion"
"SCA"      2016  3573  7 4.2491030915 "1.647" "(0.721, 3.259)" "Same"        
"STEP"     2016 11849 20 12.231363854 "1.635" "(1.027, 2.481)" "Worse"       
"WARD"     2016 64348 69 59.612604477 "1.157" "(0.908, 1.456)" "Same"        
"WARD_ONC" 2016 14767  3 18.268552618 ".164"  "(0.042, 0.447)" "Better"      
end

------------------ copy up to and including the previous line ------------------

[Jackie Reuben]

Any feedback folks?

[William Lisowski]

I think rather than putexcel, you will be better served by using putdocx or putpdf, both of which are new capabilities of Stata Version 15, which has the ability to create text documents, including tables and complex formatting, which would seem to offer the necessary flexibility to handle this fairlyc complex text in a precise fashion.

http://www.stata.com/new-in-stata/cr...ord-documents/
http://www.stata.com/new-in-stata/create-pdfs/

This is just out, however, and expertise in the use of these commands is likely not too deep here at the moment. I'm still awaiting my upgrade to grind its way through institutional inertia, for example, so I really can't comment deeply on these capabilities, as opposed to the marketing promises for them.

I think your task will consist of figuring out how to use putdocx or putpdf and rely on the SAS code only for hints on formatting - special characters, column widths, and the like. This relies strongly on Unicode for some of the special characters, so let me mention that to produce the Unicode "black star" character (★, if your browser renders it correctly) the equivalent of the SAS

#{unicode 2605}

would seem to be in Stata the function

uchar(2605)

But having created this character in a string variable, I'm unable to display the Unicode character correctly from within Stata, so I think I've exceeded the limits of my Unicode competence.

[Robert Picard]

With respect to the Unicode characters, the references are in hex so you have to use the ustrunescape() function to correctly display the characters.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 locationtype int summaryyr long centrallinedays byte events double numberpredicted str5 sir2 str14 ci str13 sig
"CC"       2016 48623 60 56.630031768 "1.06"  "(0.816, 1.354)" "Same"        
"CC_N"     2016 12688  9 17.986843553 ".5"    "(0.244, 0.918)" "Better"      
"OTHER"    2016   574  0   .811419088 "N/A"   "N/A"            "No Conclusion"
"SCA"      2016  3573  7 4.2491030915 "1.647" "(0.721, 3.259)" "Same"        
"STEP"     2016 11849 20 12.231363854 "1.635" "(1.027, 2.481)" "Worse"       
"WARD"     2016 64348 69 59.612604477 "1.157" "(0.908, 1.456)" "Same"        
"WARD_ONC" 2016 14767  3 18.268552618 ".164"  "(0.042, 0.447)" "Better"      
end

gen SIR_int = sig if sig == "No Conclusion"
replace SIR_int = "= Same" if sig == "Same"
replace SIR_int = ustrunescape("\u2605") + " Better" if sig == "Better"
replace SIR_int = ustrunescape("\u2715") + " Worse" if sig == "Worse"

* to install, type in Command window: ssc install leftalign
leftalign
list sig SIR_int, sep(0)

* The uchar() function accepts decimal values for the unicode character
dis uchar(9733) + " Better"
dis uchar(10005) + " Worse"

and the results:

. list sig SIR_int, sep(0)

     +-------------------------------+
     | sig             SIR_int       |
     |-------------------------------|
  1. | Same            = Same        |
  2. | Better          ★ Better      |
  3. | No Conclusion   No Conclusion |
  4. | Same            = Same        |
  5. | Worse           ✕ Worse       |
  6. | Same            = Same        |
  7. | Better          ★ Better      |
     +-------------------------------+

. 
. * The uchar() function accepts decimal values for the unicode character
. dis uchar(9733) + " Better"
★ Better

. dis uchar(10005) + " Worse"
✕ Worse

.

[William Lisowski]

Thanks, Robert, for marginally extending the limits of my Unicode competence. Jackie, I hope you find this discussion useful in starting you on your way to automating the process. It's an interesting programming challenge and Stata 15 would seem to make it doable.