BookmarkSubscribeRSS Feed
Wolverine
Quartz | Level 8

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;
4 REPLIES 4
Wolverine
Quartz | Level 8

I forgot to mention that when I run multiple states, only the first state is included in the Excel file.

ballardw
Super User

@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.

Reeza
Super User

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

 

 

lakshmi_74
Quartz | Level 8
I am giving some clues, You can follow this :

data have;
input adj_code year value treatment $ state $;
datalines;
0 2005 12 therap MI
1 2006 20 inpati CA
2 2007 22 inpati MI
1 2005 21 inpati MI
2 2005 22 therap CA
0 2005 10 therap CA
1 2006 19 inpati CA
2 2007 10 inpati MI
2 2006 20 therap CA
1 2006 11 inpati MI
0 2005 90 therap CA
0 2006 11 inpati MI
0 2005 99 therap MI
;
run;
proc format;
value adj_code
0="0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED"
1="1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM"
2="2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";
value $treatment
'therap'="OTHER THERAPY PERCENTAGES"
'inpati'="INPATIENT PERCENTAGES";
run;
ods escapechar='^';
ods pdf;
proc report nowd data=have spanrows;
title1 "Adjustment code claims 2.2.2, 2.3.2";
title2 "&statename., 0-18 years old";
title3 "2005-2007";
column state (adj_code) treatment,year,value;* year value treatment;
define state/group;
break after state/page;
define adj_code/'Adjustment Code' group format=adj_code.;
define treatment/''across order=internal format=$treatment.;
define year/' 'across order=internal ;
define value/'' sum;
rbreak after/ summarize;
run;
ods pdf close;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 736 views
  • 0 likes
  • 4 in conversation