I posted about a similar topic a couple months ago, and I've made some progress since then. But I'll be making a lot of these reports, so the more I can automate, the better.
I've attached an Excel file that shows what SAS actually produces, as well as a file that I had to manually edit to make it look like I want. Note that it's in landscape and I selected the option in Excel so that all columns fit on one page. Also, there is a footer that includes the report number and the date it was produced. And the column totals from Column B is copied to Column J, C is copied to K, etc.
I would appreciate any help with SAS syntax that would automate the above items, add borders, etc.
options mprint;
%macro freqs (state,statename);
LIBNAME base "D:\&state. only\base";
LIBNAME &state "D:\&state. only";
%do year=2005 %to 2012;
%macro by_ds (ds);
DATA &state..&ds.&year._&state._TEMP (compress = yes); SET base.&ds.&year._&state.;
year=input("&year",8.);
DOByr1=year-0;
DOByr2=year-18;
DOByr1c=put(DOByr1,$4.);
DOByr2c=put(DOByr2,$4.);
/**Delete cases that do not meet age requirements**/
IF EL_DOB > DOByr1c||"1231"
OR EL_DOB < DOByr2c||"0101"
THEN delete;
%mend;
%by_ds (IP)
%by_ds (OT)
ODS TRACE ON;
DATA &state..IP&year._&state._bdayCUT1 (compress = yes); SET &state..IP&year._&state._TEMP;
LENGTH ADJUST_CD_char $63;
IF ADJUST_CD = 0 THEN ADJUST_CD_char = "0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED";
IF ADJUST_CD = 1 THEN ADJUST_CD_char = "1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM";
IF ADJUST_CD = 2 THEN ADJUST_CD_char = "2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";
title "&state.&year. 2005-2012 IP Adjustment Code summary";
PROC FREQ NOPRINT;
TABLES ADJUST_CD_char /OUT=&state..IP&year._&state.ADJUST_CD_FREQ;
ODS OUTPUT OneWayFreqs(match_all)=freqs;
RUN;
ODS TRACE OFF;
DATA &state..IP&year._&state._FREQ_ADJUST_CD; SET &state..IP&year._&state.ADJUST_CD_FREQ;
&state.&year._IPcount = COUNT;
&state.&year._IPpercent = PERCENT/100;
DROP COUNT PERCENT;
RUN;
ODS TRACE ON;
DATA &state..OT&year._&state._bdayCUT1 (compress = yes); SET &state..OT&year._&state._TEMP;
LENGTH ADJUST_CD_char $63;
IF ADJUST_CD = 0 THEN ADJUST_CD_char = "0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED";
IF ADJUST_CD = 1 THEN ADJUST_CD_char = "1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM";
IF ADJUST_CD = 2 THEN ADJUST_CD_char = "2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";
title "&state.&year. OT Type Claim Code summary";
PROC FREQ NOPRINT;
TABLES ADJUST_CD_char /OUT=&state..OT&year._&state.ADJUST_CD_FREQ;
ODS OUTPUT OneWayFreqs(match_all)=freqs;
RUN;
ODS TRACE OFF;
DATA &state..OT&year._&state._FREQ_ADJUST_CD; SET &state..OT&year._&state.ADJUST_CD_FREQ;
&state.&year._OTcount = COUNT;
&state.&year._OTpercent = PERCENT/100;
DROP COUNT PERCENT;
RUN;
%end;
/*This %end statement runs all the years before merging them*/
DATA &state..IPOT_&state._ADJUST_CD_MERGE; MERGE &state..IP2005_&state._FREQ_ADJUST_CD &state..IP2006_&state._FREQ_ADJUST_CD
&state..IP2007_&state._FREQ_ADJUST_CD &state..IP2008_&state._FREQ_ADJUST_CD &state..IP2009_&state._FREQ_ADJUST_CD
&state..IP2010_&state._FREQ_ADJUST_CD &state..IP2011_&state._FREQ_ADJUST_CD &state..IP2012_&state._FREQ_ADJUST_CD
&state..OT2005_&state._FREQ_ADJUST_CD &state..OT2006_&state._FREQ_ADJUST_CD
&state..OT2007_&state._FREQ_ADJUST_CD &state..OT2008_&state._FREQ_ADJUST_CD &state..OT2009_&state._FREQ_ADJUST_CD
&state..OT2010_&state._FREQ_ADJUST_CD &state..OT2011_&state._FREQ_ADJUST_CD &state..OT2012_&state._FREQ_ADJUST_CD;
BY ADJUST_CD_char;
RUN;
ods excel file="Z:\MAX data\SYNTAX\!prcdr rev\SUMMARIES\&state. 2.2.2,2.3.2 - ADJUST_CD (1).xlsx"
/* will apply an appearance style */
style=Analysis
options(
/* for multiple procs/sheet */
sheet_interval="none"
/* name the sheet tab */
sheet_name="IPOT PRCDR"
embedded_titles = 'yes'
absolute_column_width = "28,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43"
/*auto_subtotals = 'yes' --THIS OPTION DOESN'T WORK WITH THIS TAGSET*/
hidden_columns = "2,3,4,5,6,7,8,9,18,19,20,21,22,23,24,25"
);
/* add some formatted text */
ods escapechar='~';
/*ods text="~S={font_size=14pt font_weight=bold}~ &state. 2005-2010 OT Proc code summary";*/
RUN;
proc report data=&state..IPOT_&state._ADJUST_CD_MERGE spanrows headline missing SPLIT='_';
title1 "Adjustment code claims 2.2.2, 2.3.2";
title2 "&statename., 0-18 years old";
title3 "2005-2012";
COLUMN ADJUST_CD_char
&state.2005_IPcount &state.2006_IPcount &state.2007_IPcount &state.2008_IPcount &state.2009_IPcount
&state.2010_IPcount &state.2011_IPcount &state.2012_IPcount
&state.2005_IPpercent &state.2006_IPpercent &state.2007_IPpercent &state.2008_IPpercent &state.2009_IPpercent
&state.2010_IPpercent &state.2011_IPpercent &state.2012_IPpercent
&state.2005_OTcount &state.2006_OTcount &state.2007_OTcount &state.2008_OTcount &state.2009_OTcount
&state.2010_OTcount &state.2011_OTcount &state.2012_OTcount
&state.2005_OTpercent &state.2006_OTpercent &state.2007_OTpercent &state.2008_OTpercent &state.2009_OTpercent
&state.2010_OTpercent &state.2011_OTpercent &state.2012_OTpercent;
define ADJUST_CD_char / group;
define &state.2005_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2006_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2007_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2008_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2009_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2010_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2011_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2012_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2005_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2006_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2007_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2008_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2009_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2010_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2011_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2012_IPpercent / group style(column)={tagattr='format:0.0%'};
define &state.2005_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2006_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2007_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2008_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2009_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2010_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2011_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2012_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
define &state.2005_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2006_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2007_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2008_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2009_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2010_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2011_OTpercent / group style(column)={tagattr='format:0.0%'};
define &state.2012_OTpercent / group style(column)={tagattr='format:0.0%'};
/*compute after;
PRCDR_CD_SYS="Column N";
endcomp;*/
/*compute &state.2005_PERCENT;
if upcase(_break_)='_RBREAK_' then do;
call define(_col_,"style","style={tagattr=""formula:= B8""}");*/
/*call define(_col_,"style","style={tagattr=""formula:RC[-5]""}");*/
/*call define('PRCDR_CD_SYS',"style","style={tagattr=""formula:= sum($D$4:$D9)""}");
call define('&state.2006_PERCENT',"style","style={tagattr=""formula:= sum($D$4:$D9)""}");*/
/*end;
endcomp;*/
compute after;
ADJUST_CD_char="N Column Total";
endcomp;
rbreak after / summarize;
RUN;
%mend;
/*%freqs(FL,Florida)
%freqs(IL,Illinois)*/
/*%freqs(LA,Louisiana)*/
/*%freqs(MI,Michigan)*/
/*%freqs(NY, New York)*/
/*%freqs(SC,South Carolina)*/
/*%freqs(TX,Texas)*/
%freqs(UT,Utah)
/*ODS HTML close;*/
ods excel close;
I forgot to mention that when I run multiple states, only the first state is included in the Excel file.
@Wolverine wrote:
I forgot to mention that when I run multiple states, only the first state is included in the Excel file.
Do you have a variable for the state? If so sort by that variable and use BY statevariable; to generate an output table for each level.
If not you'll have to tell us how we know when the state changes.
Asking someone to wade through that much code is probably a bit unreasonable for a forum posting.
Can you simplify your question a bit so it's more appropriate for here? A good way is to determine the feature you're looking for and seeing how you can get that built using proc report and a dataset from SASHELP. If we can't run the code then it's more difficult to help and debug. Yes, creating that is a lot of work but you're asking for a lot as well.
Here's an index of the tagset options and the corresponding pages where you can find information/examples of how to implement the feature.
https://vasug.files.wordpress.com/2011/07/excelxppaperindex.pdf
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: