BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tipscode
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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().

 

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
  • 3 replies
  • 1188 views
  • 0 likes
  • 2 in conversation