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; ... View more

I'm trying to generate an Excel summary report that looks pretty much like the file I attached. I'll be generating these reports for several states, so I'm trying to make them compact so it's easy to compare results across states.  Note that Columns C thru H are hidden.  The hidden columns contain frequencies for each year (C=2005, D=2006, etc), and these are used to generate the Total N for each year.   I'm not sure what procedure I should be using.  Proc Print is working well for me, except that I can't get it to add the totals at the end.  I also found an example using Proc Report, but the resulting Excel file isn't as close to what I want.  Many of the options in the Proc Report code don't work in Proc Print and vice versa.  The code below includes both approaches.   LIBNAME UT "D:\UT only"; proc format; VALUE $proc_sys_label "01" = "01-CPT-4 (HCPCS LEVEL 1)" "02" = "02-ICD-9-CM" "06" = "06-HCPCS (HCPCS LEVELS 2 AND 3)" "07" = "07-ICD-10 (FUTURE USE)" "10" = "10-OTHER SYSTEMS" "11" = "11-OTHER SYSTEMS" "12" = "12-OTHER SYSTEMS" "13" = "13-OTHER SYSTEMS" "14" = "14-OTHER SYSTEMS" "15" = "15-OTHER SYSTEMS" "16" = "16-OTHER SYSTEMS" "17" = "17-OTHER SYSTEMS" "18" = "18-OTHER SYSTEMS" "88" = "88-NOT APPLICABLE" "99" = "99-UNKNOWN" "" = "99-UNKNOWN"; ods excel file="Z:\MAX data\SYNTAX\!prcdr rev\OUTPUT\UT 2005-2010 IP, OT PRCDR REV freqs (4B TEST output data file).xlsx" /* will apply an appearance style */ style=Analysis options( /* for multiple procs/sheet */ sheet_interval="none" /* name the sheet tab */ sheet_name="OT PRCDR" embedded_titles = 'yes' absolute_column_width = "3,23,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57" /*auto_subtotals = 'yes' --THIS OPTION DOESN'T WORK WITH THIS TAGSET*/ hidden_columns = "3,4,5,6,7,8" /*"C,D,E,F,G,H"*/ ); /* add some formatted text */ ods escapechar='~'; /*ods text="~S={font_size=14pt font_weight=bold}~ UT 2005-2010 OT Proc code summary";*/ PROC PRINT DATA = UT.OT_UT_FREQ_MERGE; label UT2005_PERCENT = 'UT2005*PERCENT' UT2006_PERCENT = 'UT2006*PERCENT' UT2007_PERCENT = 'UT2007*PERCENT' UT2008_PERCENT = 'UT2008*PERCENT' UT2009_PERCENT = 'UT2009*PERCENT' UT2010_PERCENT = 'UT2010*PERCENT'; var PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT UT2010_COUNT; var UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT UT2009_PERCENT UT2010_PERCENT / style(data)={tagattr='format:0.00%'}; /* formula:SUM(R3C3:R10C8*/ RUN; proc report data=UT.OT_UT_FREQ_MERGE spanrows; COLUMN Obs PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT UT2010_COUNT UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT UT2009_PERCENT UT2010_PERCENT; define PRCDR_CD_SYS / group; define UT2005_COUNT / group; define UT2006_COUNT / group; define UT2007_COUNT / group; define UT2008_COUNT / group; define UT2009_COUNT / group; define UT2010_COUNT / group; define UT2005_PERCENT / group; define UT2006_PERCENT / group; define UT2007_PERCENT / group; define UT2008_PERCENT / group; define UT2009_PERCENT / group; define UT2010_PERCENT / group; compute after; PRCDR_CD_SYS="Total N"; endcomp; compute UT2005_COUNT; if upcase(_break_)='_RBREAK_' then do; call define(_col_,"style","style={tagattr=""formula:= sum($C$4:$C9""}"); end; endcomp; rbreak after / summarize; RUN; ods excel close; ... View more

I'm combining 2 files by an ID variable.  Each of the input files has approximately 5000 records, and none of them have missing data for the ID variable.  The output file has about 7400 records, about 800 of which have missing data for the ID variable.  How can that be?   Proc SQL;     Create Table &state..&state.&year._IMG8_matched_DX_IMAGED     as Select a.MSIS_ID, a.DX, b.MSIS_ID, b.IMGPROC, a.SRVC_BGN_DT, b.SRVC_BGN_DT,         a.SRVC_BGN_DT as DxDatenum,         b.SRVC_BGN_DT as IMGDatenum     from  &state..ipot&year._&state._IMGmeas8_DX as a     RIGHT JOIN &state..ipot&year._&state._IMGmeas8_IMAGED as b     ON a.MSIS_ID = b.MSIS_ID; quit; ... View more

I have files that include a variable that lists diagnosis codes associated with various medical specialties.  I need to compare values from another file to determine if a given record included a diagnosis that matched the medical specialty.  I've been copying and pasting the list directly into the syntax: IF DX in ("03282","03640"....) THEN cardiology_flag = 1; This works but is unwieldy and difficult to update.  There are 10 specialties with 800-2500 codes each! The list is stored in a file named as such: DXcodes_cardiology.sas7bdat.  There are 2 variables in this file that contain valid codes, named DX_5A and DX_5B.  So how do I get SAS to look at a record in a file called DXcardiology._spec_cat.sas7bdat, and compare the the value stored in the variable DX to the list of codes in the variables DX_5A and DX_5B in DXcodes_cardiology.sas7bdat, and then give the variable cardiology_flag a value 1 if the code is present in the list, and a value of 0 if it's not present in the list? ... View more