Macro Variable Manipulation Examples

Example Data

The library "Medical" has the following tables, columns, and values. These are dimensional tables, that is, they have unique rows for each entity.

Clinics

Clinic_ID     Clinic_Name

01            Main

02            West

03            East

04            Othertown

Providers

Provider_ID  Provider_Name

1001         Dr. John A

1002         Dr. Jim B

1003         Dr. Jane C

1004         Dr. Jessica D 

IntoList

To generate a macro variable with a list of all Clinic_ID values or Provider_ID values:

%IntoList(Clinics, Clinic_ID, ClinicList, sepby=COMMA);

The macro will generate 1 macro variable: ClinicList = 01, 02, 03, 04

%IntoList(Providers, Provider_ID, ProviderList, sepby=QQC);

The macro will generate 1 macro variable: ProviderList = "1001", "1002", "1003", "1004"

Note: These macro variables are good for using in IN lists. Check the macro documentation for additional delimiters (sepby=).

ObsMac

To generate multiple macro variables, one for each clinic or provider:

%ObsMac(Clinics, Clinic_ID, Clinic);

The macro will generate 4 macro variables with the values: Clinic1 = 01 Clinic2 = 02 Clinic3 = 03 Clinic4 = 04

Note: These macro variables are good for using in macro loops that iterate based on macro variable names. For example:

%macro loop;
  %local i;
  /* Note: ClinicCNT is generated by the ObsMac macro program */
  %do i=1 %to &ClinicCNT;
    %let current=&&CLINIC&I;
    proc sql;
      create table Clinic_&CURRENT._Patients as
      select *
      from patients
      where clinic_id="&CURRENT"
      ;
    quit;
  %end;
%mend loop;
%loop;

SetVars

To generate macro variables for each column names in a table:

%SetVars(Clinics, ClinicVars);

The macro will generate 2 macro variables with the values: ClinicVars1 = Clinic_ID ClinicVars2 = Clinic_Name

%SetVars(Clinics, ClinicVars, type=LIST);

The macro will generate 1 macro variable: ClinicsVars = Clinic_ID Clinic_Name

Note: The type is set to MULTI by default; thus, it is not necessary to specify it in the first SetVars example.

TableVars

To set the tables themselves to macro variables:

%TableVars(Medical, table);

The macro will generate 2 macro variables with the values:

table1 = Clinics table2 = Providers

Note: This is also good for looping (see below). This macro is essentially a copy of ObsMac with a fixed data source (SASHELP.VTABLE).

%macro loop;
  %local i;
  /* Note: TABLECNT is generated by the TableVars macro program */
  %do i=1 %to &TABLECNT;
    %let current=&&TABLE&I;
    data _temp_;
      set &CURRENT;
    run;
    /* Additional code... */
  %end;
%mend loop;
%loop;

VarMac

To create macro variables with their names based on values in a column and the macro variable values based on values in another column:

%VarMac(Clinics, Clinic_Name, Clinic_ID);

The macro will generate 4 macro variables with the values: Main = 01 West = 02 East = 03 Othertown = 04

Note: This can be useful to convert one type of identification to another. It is also useful to specify the source for a query and still be able to change it if necessary (e.g., a macro variable named "source" with the value "table_a").