Categories‎ > ‎

Macro Variable Manipulation

  1. Delete similar macro variables that already exist to avoid conflicts.
  2. Create the specified macro variables in the specified manner.
  3. Report the macro variables created in a single list using SASHELP.VMACRO.
  4. Allow for filtering of the input data (if possible).
The following macro programs create macro variables for later reference within SAS code.

ObsMac

Set observations in a data set to macro variables, using a specified prefix and the observation number to create the macro variable name. Optionally filter the input data set.

TableVars

Generate macro variables for tables identified from SASHELP.VTABLE, naming the variables using the specified prefix and the observation number of the table. Useful when processing multiple data sets. Similar to ObsMac, but with a static source table.

IntoList

Create a macro variable that is a list of values from a column in a data set. Optionally define the delimiter and filter the input data set.

VarMac

Create macro variables from two columns, where one column names the macro variable and another supplies the value. Optionally filter the input data set.

SetVars

Create one or more macro variables from the variable names in a data set. The generated macro variable can either be a list within one macro variable or multiple macro variables named with the specified prefix and appended observation number.

DelVars

Delete all user macro variables. This is best used to clear out macro variables before moving on to another process in order to avoid conflicting or mistaken values. A list of macro variables can be excluded from deletion.

Output Types

Macro Program
Arguments
Output Type
IntoList

Single macro variable consisting of a list
ObsMac

1-to-many macro variables with appended numbers
SetVars
type=MULTI
type=LIST
1-to-many macro variables with appended numbers
Single macro variable consisting of a list
TablesVars

1-to-many macro variables with appended numbers
VarMac

1-to-many macro variables

Examples

The library "Medical" has the following tables, columns, and values:

Clinics

Providers

Clinic_ID
Clinic_Name
  Provider_ID
Provider_Name
01
Main   1001 Dr. John A
02 West   1002 Dr. Jim B
03 East   1003 Dr. Jane C
04 Othertown   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").