SAS variable labels in macro

Question

I have a SAS dataset with some variable, and I wish to run PROC TABULATE for each one of them. I wrote a macro to do that, but the macro prints the name of the variables, while I would like to print their label, and I do not know how to change that. My macro is:

%macro Frequency(data, variable);
title2 "Frequency Distribution of &variable";
proc tabulate data = &data;
    class treatment &variable;
    table (treatment = '' all),(&variable = '' all)*(n*f=8. rowpctn='%'*f=8.1)/box = "Treatment Group/&variable";
run;
title2;
%mend Frequency;

The problem is, that some variables has a label of more than one word, and I can't get an output with a title aaa_hhh, it has to be properly aaa hhh.

Another idea I had was to transpose the data into a "long" format, and to use the "by" word to run the tabulation by variable, however, this failed too, since I want the variable name in the box, and "ByVal1" doesn't support that option. Can you please assist me here? Thank you.

Answer 1

Extract the label using either vlabel within a data _null_ step OR reading SASHELP.VCOLUMN.

data _null_;
set &data (obs=1);
call symputx('var_label', vlabel(&variable));
run;

&put &var_label;

Answer 2

Here is a macro function which will let you extract the label:

%macro getVarLabel(ds=sashelp.class /* two level ds name */
      , var= /* variable name for which to return the label */
    );
  %local dsid vnum vlabel rc;
  /* Open dataset */
  %let dsid = %sysfunc(open(&ds));
  %if &dsid > 0 %then %do;
    /* Get variable number */
    %let vnum = %sysfunc(varnum(&dsid, &var));
    %if(&vnum. > 0) %then
       /* Variable exists, so get label */
       %let vlabel = %sysfunc(varlabel(&dsid, &vnum));
    %else %do;
       %put NOTE: Variable &var does not exist in &ds;
       %let vlabel = %str();
    %end;
  %end;
  %else %put dataset &ds not opened! (rc=&dsid);

  /* Close dataset */
  %let rc = %sysfunc(close(&dsid));

  /* Return variable label */
  &vlabel

%mend;

You can then call this directly in your title statement:

title2 "Frequency Distribution of %getVarLabel(ds=&data,var=&variable)";

Answer 3

Calling the macro n times for n variables seems like a pain. This is UNTESTED code - if you add data to the question, I'll debug it with your sample data.

Obviously, you won't run "treatment" against "treatment". Maybe there are other variables you don't actually run PROC TABULATE on. Maybe there is also a SubjectID variable, or a TreatmentDate variable that you don't run against. I'll assume you will call the macro with the dataset name (including libref, if applicable) a comma, and then the list of variables not to include in the Tabulate, separated by spaces.

%MACRO frequency(data,varexclude) ;

  %* remove extra spaces, just in case ;
  %LET varexclude = %SYSFUNC(COMPBL(&varexclude)) ; 

  %* swap each remaining space for double-quote comma double-quote ;
  %LET varexclude = %SYSFUNC(TRANWRD(&varexclude,%STR( ),%STR(",")) ; 

  %* Get all the varable names, labels, types, etc ;

PROC CONTENTS DATA=&data OUT=vlist NOPRINT ;
RUN ;

  %* Put the variable names and types into macro variables ;

PROC SQL ;
  SELECT name, label
    INTO :varlist SEPARATED BY '|',
         :lablist SEPARATED BY '|'
    FROM vlist
    WHERE UPCASE(name) NOT IN (%UPCASE("&varexclude"))
      AND type EQ 1
  ;  %* type EQ 1 means numeric.  If you want TABULATE on character, too, remove that part of the WHERE clause ;

  %* how many tabulates will we run ? ;
  %LET numvars = &SQLOBS ;  
QUIT ;

  %DO i = 1 %TO &numvars ;

title2 "Frequency Distribution of %SCAN(&lablist,&i,|)";
proc tabulate data = &data;
    class treatment %SCAN(&varlist,&i,|);
    table (treatment = '' all),(%SCAN(&varlist,&i,|) = '' all)*(n*f=8. rowpctn='%'*f=8.1)/box = "Treatment Group/%SCAN(&lablist,&i,|)";
run;

  %END ;
%MEND