How to use a macro to generate htms across years

[tipscode]

I need to generate 50+ htm tabs (by state) across at least 3 years using a large dataset in SAS 9.4. Currently I am generating the 50+ tabs year by year, but would like to try a nested macro so that multiple consecutive years would be run (e.g., 2013, 2014, 2015). I am also having problems creating a macro variable using the numeric variable "year". Below is the current code. Any comments or suggestions are much appreciated!

%include "&path\tableEditor.tpl"; 
run;

%macro testtab(st,xst1);ods tagsets.tableeditor file="&path\&xst1..htm"options( 
       frozen_headers="yes"       frozen_rowheaders="yes"       banner_color_even="beige"       banner_color_odd="white"       header_bgcolor="teal"       header_fgcolor="white"       rowheader_bgcolor="lightblue"       gridline_color="gray"       header_size="12"  
       rowheader_size="12" 
       data_size="11";

  %macro testtab(st,xst1);  ods tagsets.tableeditor file="&path\&xst1..htm"  options( 
       frozen_headers="yes"       frozen_rowheaders="yes"       banner_color_even="beige"       banner_color_odd="white"       header_bgcolor="teal"       header_fgcolor="white"       rowheader_bgcolor="lightblue"       gridline_color="gray"       header_size="12"  
       rowheader_size="12" 
       data_size="11" 
       /*sheet_name="&xst1"*/
      );  PROC TABULATE NOSEPS MISSING FORMAT=comma7. FORMCHAR='              '  
  data=t;  CLASS STATEFIP YR sub1;
  class age sex race ethnic / style={background=lightyellow}; tables statefip, all*(n f=5.1*pctn<sub1 all>) 
 (sex all)*(pctn<sex all>)*f=5.1 
 (age all)*(pctn<age all>)*f=5.1 
 (race all)*(pctn<race all>)*f=5.1 
 (ethnic all)*(pctn<ethnic all>)*f=5.1, 
 all sub1 / rts=16 misstext='--' box=_page_ printmiss; format age ageg. sex sex. race race. ethnic ethnic. sub1 sub. statefip   
 $state.; keylabel all='Total' n='No.' pctn='%'; title1  height=11pt 'Title A,'; title2  height=11pt 'Title B';   
 title3  height=11pt 'Year = 2013'; */Need to make this a macro*/

 where statefip IN (&st);   

 run;  

 ods tagsets.tableeditor close;

 %mend testtab;

 %testtab('AL',AL15)
 %testtab('AR',AR15)...;run;

[Tom · Accepted solution]

First make your macro a little more flexible.  For example I would make it so that you have a ST and YR parameter that can take single values or space delimited values. I would also make the input dataset and output folder parameters. You could also make the output filename a parameter, but default to a value that is based on the ST and YR values.

%macro testtab(st,yr,filen=,dsn=t,loc=&path); 
%if 0=%length(&st) %then %let st=ALL ;
%if 0=%length(&yr) %then %let yr=ALL ;
%if 0=%length(&filen) %then %let filen=%sysfunc(translate(&yr\&st,'_',' '));

title1  height=11pt 'Title A,'; 
title2  height=11pt 'Title B';   
title3  height=11pt "STATE= &st  YEAR= &yr;"; 

ods tagsets.tableeditor file="&loc\&filen..htm" 
  options( 
    frozen_headers="yes"
    frozen_rowheaders="yes"
    banner_color_even="beige"
    banner_color_odd="white"
    header_bgcolor="teal"
    header_fgcolor="white"
    rowheader_bgcolor="lightblue"
    gridline_color="gray"
    header_size="12"  
    rowheader_size="12" 
    data_size="11" 
    /*sheet_name="&xst1"*/
  )
;  

PROC TABULATE data=&dsn NOSEPS MISSING FORMAT=comma7. FORMCHAR='              ' ;
%if ("&st" ne "ALL") %then %do;
  where also statefip IN ("%sysfunc(tranwrd(%sysfunc(compbl(&st)),%str( )," "))");
%end;
%if ("&yr" ne "ALL") %then %do;
  where also yr in (&yr);
%end;
  CLASS STATEFIP YR sub1;
  class age sex race ethnic / style={background=lightyellow};
  tables statefip
       , all*(n f=5.1*pctn<sub1 all>) 
        (sex all)*(pctn<sex all>)*f=5.1 
        (age all)*(pctn<age all>)*f=5.1 
        (race all)*(pctn<race all>)*f=5.1 
        (ethnic all)*(pctn<ethnic all>)*f=5.1
       , all sub1 
     / rts=16 misstext='--' box=_page_ printmiss
   ; 
   format age ageg. sex sex. race race. ethnic ethnic. sub1 sub. statefip  $state.;
  keylabel all='Total' n='No.' pctn='%';
run;
ods tagsets.tableeditor close;
%mend testtab;

You might need to make other parts of the PROC TABULATE code flexible so that it adjusts when the table represents more than one year or more than one state.

Then you can generate calls to create all years for a single state or all states for a single year or any combination.

%let path=\\server\sharename\foldername;
options mprint;

%testtab(AL,2013)
%testtab(AL,2014)
%testtab(st=CA)
%testtab(yr=2013)

Or you could query the data in your input table and generate all possible combinations.

proc sql noprint;
select cats('%testtab(',statefip,',',yr,')')
  into :calls separated by ';'
  from (select distinct statefip from T)
     , (select distinct yr from T)
;
quit;

&calls;

[tipscode]

Thank you so much Tom!

Two follow-up questions:

1) Currently all states are spelled out in the original dataset (as variable "statefip", with individual value such as "Alabama"), and a label is applied for the abbreviation ( attrib statefip label='STATE: '; ). The original macro uses "where statefip IN (&st)" - Would this work with the changes that you recommended?

2) Can a macro be used to create each htm's name as "State Abbreviation and Year", e.g., "HI15"?

[Tom]

I am not sure about your questions but you can adjust the macro to generate any SAS code that you can figure out.

From your use of the FORMAT statement in the PROC TABULATE code I assumed that the values were already stored as the abbreviations and that the format STATE that you mentioned would cause the longer state name to be used in the report.

If the values in the data have full state names ('Alabama', 'New York', etc.) and you want to support passing multiple values into the macro then you will need to adjust the logic to use some other separator than a space in the list of values.   Or you could use formats or functions to convert between code and state name so that you could still pass in the shorter value.

You should look at some of the functions that SAS has for handling FIPS codes.  Like FIPSNAME() and STFIPS().