BUILDING A REPORT ADDING A VARIABLE EACH MONTH.

[TracyG]

A

I am using proc report to create a excel file.    every month I copy and paste the define states and change the names to the new month.   for example this month my variable names have apr16, mar16, feb16, apr15,mar15,feb16 in them.   Next month may16 and may15 will be created in the dataset.     I want to try and automate this so I do not have to copy and paste, each month. 

This is what I currently doing.

%marco report(currmnth1,currmnth2,.....)

proc report.......

define den_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Den
&currmnth1.";

define num_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Num
&currmnth1.";

define rate_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Rate
&currmnth1.";

define den_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Den
&currmnth2.";

define num_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Num
&currmnth2.";

define rate_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Rate
&currmnth2.";

..... then next month I would copy statements above and paste then change to &currmnth3, .....

%mend report;
%report(mar16,apr16);

next month could be:

%report(mar16,apr16,may16);

[Reeza]

What does the rest of your report look like? Is it an option to restructure the data so that the months become an ACROSS variables so you aren't making these changes in the first place?

[TracyG]

I am not sure if that will work.   will have to take a look at that.

Here is the rest of the code.

proc sort data=mnth.&hedyr._&currbuild.;

by/*category*/ lob meas_sub;run;

%marco report(currmnth1,currmnth2,.....)

ods listing close;

options leftmargin = .4in
rightmargin = .4in TopMargin = .4in
BottomMargin = .4in /*validmemname=extend*/
validvarname=any;

ods tagsets.excelxp
file = "/data/dept/hsvcsqi/Delta/HEDIS_2015/Bill_Sheet/Delta/&hedyr._&currbuild._&date1..xls"
style = styles.rtf

options (
sheet_name  = 'Comparison' sheet_interval='none'

orientation = 'landscape'
embedded_Titles = 'yes' frozen_headers='1'/* pages_fitweight = '1'*/

pages_fitheight = '2'
autofit_height = 'yes' row_Repeat = '1-1'
scale = '80' center_horizontal = 'yes'/*print_headers = 'on'*/

absolute_column_width ='35,15,20,30,15,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,

6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6');

proc report data=mnth.&hedyr._&currbuild
nowd style(header)=[fontweight=bold fontsize=7pt];

define guid /display
style = {just=left font = ("Times New Roman",7pt)}'GUID';

define lob /display
style = {just=left font = ("Times New Roman",7pt)} 'LOB';

define population
/display style = {just=left font = ("Times New
Roman", 7pt)}'Population';

define meas_sub
/display style = {just=left font = ("Times New
Roman",7pt)}'Meas_Sub';

define key /display
style = {just=left font = ("Times New Roman",7pt)}'Meas_Key';

define den_feb16
/display style = {just=left font = ("Times New
Roman",7pt)}'Den
Feb16';

define num_feb16
/display style = {just=left font = ("Times New
Roman",7pt)}'Num
Feb16';

define rate_feb16
/display style = {just=left font = ("Times New
Roman",7pt)}'Rate
Feb16';

define den_feb15
/display style = {just=left font = ("Times New
Roman",7pt)}'Den
Feb15';

define num_feb15
/display style = {just=left font = ("Times New
Roman",7pt)}'Num
Feb15';

define rate_feb15
/display style = {just=left font = ("Times New
Roman",7pt)}'Rate
Feb15';

define
den_feb16_feb15_chg /display style = {just=left font = ("Times
New Roman",7pt)}'DEN_Feb16
Feb15 Chg';

define
den_feb16_feb15_P_chg /display style = {just=left font = ("Times
New Roman",7pt)}'DEN_Feb16
Feb15 % Chg';

define
num_feb16_feb15_chg /display style = {just=left font = ("Times
New Roman",7pt)}'NUM_Feb16
Feb15 Chg';

define
num_feb16_feb15_P_chg /display style = {just=left font = ("Times
New Roman",7pt)}'NUM_Feb16
Feb15 % Chg';

define
rate_feb16_feb15_chg /display style = {just=left font = ("Times
New Roman",7pt)}'RATE_Feb16
Feb15 Chg';

define
rate_feb16_feb15_P_chg /display style = {just=left font = ("Times
New Roman",7pt)}'RATE
Feb16 Feb15 % Chg';

define den_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Den
&currmnth1.";

define num_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Num
&currmnth1.";

define rate_&currmnth1.
/display style = {just=left font = ("Times New
Roman",7pt)}"Rate
&currmnth1.";

define den_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Den
&currmnth2.";

define num_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Num
&currmnth2.";

define rate_&currmnth2.
/display style = {just=left font = ("Times New
Roman",7pt)}"Rate
&currmnth2.";

[TracyG]

I took a different approach and used proc datasets with contents and created a string of the variable headings in the dataset.   Then used the string to create the columns in proc reports.  Works great.

proc datasets library=mnth nolist;

contents data=&hedyr._&currbuild._1 varnum short out=varlist ; run;

proc sort data=mnth.varlist(keep=name varnum);

by varnum;

run;

proc sql noprint;

  select name into :var_string separated by ' '

          from mnth.varlist

; quit;

%put &var_string;

Proc report data=mnth..........;

column &var_string;