Making Variable Values Private in SQL

Here's another usage of the file info functions. I wrote this because I wanted to display claims records on a web page, but without any personal information. Various data sets might be displayed, and they might not all have all of the variables I wanted to make private. I didn't want to write a separate SQL statement for each of the dozens of data sets, so I wrote a utility macro.

Task: Given a data set which contains soc_sec_num and other variables, create a select clause which masks soc_sec num but displays the other variables normally. Extend that to an arbitrary number of privatized variables and a large number of data set variables.

Here's my solution:

%macro mtPrivatizeSqlVars(
      data=,
      privatevars=soc_sec_num prime_prsn_ssn last_nm first_nm,
      newvalues=0 0 Lastname Firstname);
/* 10-Dec-2004 Jack Hamilton                                   */
/* Macro:      mtPrivatizeSqlVars                              */
/*                                                             */
/* Purpose:    Create a list of variables in a data set,       */
/*    separated by commas and suitable for use in an SQL       */
/*    SELECT statement, where values of specified vars are     */
/*    replaced by a fixed string.                              */
/*                                                             */
/* Parameters:                                                 */
/*    Data - the name of the dataset (libref.member)           */
/*    PrivateVars - list of variables to be privatized,        */
/*             separated by spaces                             */
/*    NewValues - values to be used instead of private values  */
/*                                                             */

   %local i j dsid nvars varlist thisvarname lowvarname varlabel vartype privatewordpos newtext thisword;

   %let privatevars = %lowcase(&PRIVATEVARS.);

   %let varlist = ERROR IN mtPrivatizeSqlVars MACRO;

   /* Open the dataset.  */

   %let dsid = %SYSFUNC(open(&DATA., I));
   %if &DSID = 0 %then
      %do;
      %let varlist = ERROR IN mtPrivatizeSqlVars MACRO: %sysfunc(sysmsg());
      %put ERROR: &VARLIST.;
      %goto EXIT;
      %end;

   %let nvars = %SYSFUNC(attrn(&DSID., nvars));
   %let varlist = ;

   %do i = 1 %to %EVAL(&NVARS. - 1);
      %let thisvarname = %SYSFUNC(varname(&DSID. ,&I.));
      %let lowvarname = %LOWCASE(&thisvarname);
      %let privatewordpos = 0;

      /* Find location of varname in private list. */
      %do j = 1 %to 999;
         %let thisword = %lowcase(%scan(&PRIVATEVARS., &J., %str( )));
         %if "&THISWORD." = "" %then %goto nomorewords;
         %if "&THISWORD." = "&LOWVARNAME." %then
            %do;
            %let privatewordpos = &J.;
            %goto nomorewords;
            %end;
      %end;
      %NOMOREWORDS:

      %if &PRIVATEWORDPOS. = 0 %then
         %let varlist = &VARLIST. &THISVARNAME. ,;
      %else
         %do;
         %let varlabel = %SYSFUNC(varlabel(&DSID. ,&I.));
         %let vartype = %SYSFUNC(vartype(&DSID. ,&I.));
         %let newtext = %scan(&NEWVALUES., &PRIVATEWORDPOS., %str( ));
         %if "&VARLABEL." = "" %then %let varlabel = &THISVARNAME.;
         %if &VARTYPE. = N %then
            %let varlist = &VARLIST. &NEWTEXT. as &THISVARNAME. label="&VARLABEL.", ;
         %else
            %let varlist = &VARLIST. "&NEWTEXT." as &THISVARNAME. label="&VARLABEL.", ;
         %end;
   %end;

   %let thisvarname = %SYSFUNC(varname(&DSID. ,&I.));
   %let lowvarname = %LOWCASE(&thisvarname);
      %if %sysfunc(indexw(&PRIVATEVARS., &LOWVARNAME.)) = 0 %then
         %let varlist = &VARLIST. &THISVARNAME.;
      %else
         %do;
         %let varlabel = %SYSFUNC(varlabel(&DSID. ,&I.));
         %let vartype = %SYSFUNC(vartype(&DSID. ,&I.));
         %let newtext = %scan(&NEWVALUES., &PRIVATEWORDPOS., %str( ));
         %if "&VARLABEL." = "" %then %let varlabel = &THISVARNAME.;
         %if &VARTYPE. = N %then
            %let varlist = &VARLIST. &NEWTEXT. as &THISVARNAME. label="&VARLABEL." ;
         %else
            %let varlist = &VARLIST. "&NEWTEXT." as &THISVARNAME. label="&VARLABEL." ;
         %end;

   /* Close the dataset.                                          */

%EXIT:

   %let rc = %SYSFUNC(close(&DSID.));

   &VARLIST.

%mend mtPrivatizeSqlVars;

data test;
   last_nm = 'Hamilton'; soc_sec_num = 123456789; 
   incrd_da = '12dec2004'd; paid_amnt = 75.00; 
   proc_cd = '99213';
   format incrd_da worddatx12.;
   output;
run;

%let changedvars = %mtprivatizesqlvars(data=test);
%put &CHANGEDVARS.;

proc sql;

   select   &CHANGEDVARS.
   from     test
   ;

quit;

Relevant log:

2023
2024  %let changedvars = %mtprivatizesqlvars(data=test);
2025  %put &CHANGEDVARS.;
"Lastname" as last_nm label="last_nm", 0 as soc_sec_num label="soc_sec_num", incrd_da , paid_amnt , proc_cd

Output:

last_nm   soc_sec_num      incrd_da  paid_amnt  proc_cd
Lastname            0   12 Dec 2004         75  99213

I then use the macro in an htmSQL page. htmSQL works better in this case than a SAS program in the broker would, because it's easy to generate a table with two header rows, one for the variable name and one for the variable label.