Code that writes code that calls itself

There is a known issue in SAS 9.4M3 when writing out a SAS data set using the ODS EXCEL statement. The issue is that digits to the right of the decimal point are lost when using the PERCENTw.d format. While the problem has been resolved in SAS 9.4M4, I do not have that release.

The below macro code was written to resolve this specific issue, so modify as needed. Also consider that the techniques used here may come into play for other ancillary tasks.

The solution was to write out a VAR statement for each variable in PROC PRINT and conditionally apply the TAGATTR= style attribute. In this case, I elected to have code that writes code to a TEMP filename and then calls itself. The TEMP filename associates with an external file stored in the WROK library so it goes away when the session goes away. Instead of using PROC SQL with dictionary tables to extract the metadata and then pass thru it a second time, I opted to use SAS component language or SCL functions availabe in Base SAS to do the same in a single pass.

What you see next is an example of the generated output.

proc print data = mydsn noobs ;
  var numericvar ;
  var perct1  / style( data ) = { tagattr = "format:###.00\%" } ;
  var perct2  / style( data ) = { tagattr = "format:###.00\%" } ;
run ;

Below is the actual source code to handle this situation.

%macro printpct( dsn = ) ;
  %if not %sysfunc( exist( &dsn. ) ) %then %do ;
    %put %str(E)RROR: &dsn. is not a valid data set. ;
    %return ;
  %end ;
 
  filename pgm temp ;
  data _null_ ;
    file pgm ;
    put "proc print data = &dsn. noobs ;" ;
    dsid = open( "&dsn." ) ;
    if dsid then do i = 1 to attrn( dsid, 'nvars' )  ;
      name = varname( dsid, i ) ;
      put '  var ' name @ ;
      if varfmt( dsid, i ) = 'PERCENT8.2' then 
        put ' / style(data) = {tagattr="format:###.00\%"}' @;
      put " ;" ;
    end ;
    dsid = close( dsid ) ;
    put "run ; " ;
  run ;
  %include pgm ;
  filename pgm clear ;
%mend ;