This SAS code processes survey data and generates a multi-sheet...

August 27, 2025 at 08:33 PM


*/Summary Tab*/;

Ods graphics on;
ods noproctitle;
proc freq data=surveys noprint;
	tables Report / norow nocol nopercent nocum out=Summary;
run;
data summary;
	set summary;
	Percent_Freq= round(percent, 3.0);
	drop percent;
run;

*/Export to excel*/;

ods excel file="&outpath/&SYSDate._IssuesReport_Combined_Compliance.xlsx" style=styles.htmlblue options(sheet_name="Summary");
proc print data=Summary;
run;


ods excel style=styles.htmlblue options(sheet_name="Issues");
proc report data=Issues;
column _all_;
compute Var23; if Var23 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var24; if Var24 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var25; if Var25 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var26; if Var26 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var27; if Var27 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var28; if Var28 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var29; if Var29 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var30; if Var30 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var31; if Var31 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var32; if Var32 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var33; if Var33 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var34; if Var34 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var35; if Var35 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var36; if Var36 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var37; if Var37 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var38; if Var38 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var39; if Var39 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var39 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var40; if Var40 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var40 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var41; if Var41 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var41 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var42; if Var42 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var42 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var44; if Var44 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var44 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var45; if Var45 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var45 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var47; if Var47 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var47 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var48; if Var48 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var48 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var50; if Var50 = "No" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var52; if Var52 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var52 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var53; if Var53 = "Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]");
else if Var53 = "Very Dissatisfied" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var55; if Var55 = "IIS resources" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var56; if Var56 = "Scheduling resources" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var57; if Var57 = "Vaccine communications resources" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;
compute Var58; if Var58 = "Strong vaccine recommendation resources" then call define (_col_, "Style", "style=[Background=Red]"); endcomp;

run;

ods excel style=styles.htmlblue options(sheet_name="Comments");
proc print data=Comments noobs label;
run;

ods excel style=styles.htmlblue options(sheet_name="RawData");
proc print data=surveys noobs label;
run;

ods excel close;

This SAS code processes survey data and generates a multi-sheet Excel report, highlighting specific data conditions. Here's a breakdown of what the code does:

Summary Tab Processing

ODS Graphics and Titles Setup:
- Turns on ODS (Output Delivery System) graphics and disables the default procedure title (ods graphics on and ods noproctitle).
Frequency Analysis:
- Uses the proc freq procedure to compute frequencies of the Report variable in the surveys dataset. The output is written to a new temporary dataset called Summary, without displaying the results.
Data Manipulation:
- Modifies the Summary dataset by:
  - Rounding the percent variable to three decimal places and storing it in Percent_Freq.
  - Dropping the original percent variable.

Export to Excel

Initialize Excel Export:
- Defines an Excel file name, including a date from the system macro variable (&SYSDate), and specifies the sheet styles and names.
Sheet: Summary:
- Writes the Summary dataset to the first Excel sheet named "Summary" using the proc print procedure.
Sheet: Issues:
- Processes the Issues dataset using proc report and applies conditional formatting:
  - Highlights rows by setting the background color of certain variables (Var23 to Var58) to red based on specific conditions. For example:
    - Variables like Var23 to Var38 are highlighted red if their value is "No".
    - Variables like Var39 to Var45 are highlighted red if their value is "Dissatisfied" or "Very Dissatisfied".
    - Variables like Var55 to Var58 are highlighted red based on specific string matches (e.g., "IIS resources", "Scheduling resources").
Sheet: Comments:
- Writes the contents of the Comments dataset to a sheet named "Comments" using proc print. The options suppress observation numbers and include labels.
Sheet: RawData:
- Writes the entire surveys dataset to a sheet named "RawData" using proc print. Observation numbers are suppressed, and labels are included.
Close Excel Destination:
- Closes the Excel ODS output to ensure all data is written properly.

Final Report

The generated Excel file contains multiple sheets:

Summary – Summary table of frequencies for the Report variable with percentages rounded to 3 decimal places.
Issues – A detailed report from the Issues dataset with rows/columns conditionally highlighted in red based on specific values.
Comments – Contents of the Comments dataset.
RawData – Entire contents of the surveys dataset.

The conditional formatting helps highlight problem areas (specific responses) in the "Issues" sheet.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr