Put distinct values of a variable in a SAS dataset to a macro variable

As a SAS programmer, one would encounter many instances where one is required to create a comma or a pipe delimited list of dataset values from a dimension dataset and use that list to pull in records from a detailed transactions dataset…Usually that list is used in a SQL query or a Datastep’s IN clause. For eg. Get a subset list of car models of interest and query another fact transactions table to get all the cars sold for that model by month.

This macro below can easily build that for those kind of instances.

/**************************************************************************************************************

* Macro_Name:   put_distinct_varvalues_in_list                                                                       

*

* Purpose: Macro to put distinct dataset variable values as a quoted list in a macro variable

*         

*                                                                                                             

* Usage: %put_distinct_varvalues_in_list;

*

* Input_parameters: dsn - Name of the dataset

*                   var - Variable in the dataset whose distinct values are required in list format

*                   mac_var - Name of the macro variable to store the list

*                   dlm - delimiter in the list...comma is the default

*                   quotes - what type of quoting is reqd...double quote is the default

*                                                                                                             

* Outputs:  None.                                                                                              

*                                                                                                             

* Returns:  None  

*

* Example:

*                                                                                                             

* Modules_called: None

*

* Maintenance_History:                                                                                       

*-------------------------------------------------------------------------------------------------------------*

*  Date:      |   Who:        |  Description:                                                                 *

*-------------------------------------------------------------------------------------------------------------*

* 10/14/2014  | Sharad           | Initial creation.                                                          *

*-------------------------------------------------------------------------------------------------------------*

* HeaderEnd:                                                                                                  *

**************************************************************************************************************/

%macro put_distinct_varvalues_in_list(dsn,var,mac_var,dlm=comma,quotes=double);

     %global &mac_var.;

     %if %upcase(&dlm) eq COMMA %then %let dlm=%str(,);

     %else %if %upcase(&dlm) eq PIPE %then %let dlm=%str(|);

       %else %if %upcase(&dlm) eq SPACE %then %let dlm=%str( );

     proc sql noprint;

     select DISTINCT

     %if %upcase(&quotes) eq SINGLE %then %do; "'"||&var.||"'" %end;

     %else %if %upcase(&quotes) eq DOUBLE %then %do; quote(&var.) %end;

       %else %if %upcase(&quotes) eq NONE %then %do; (&var.) %end;

     %else &var.;

     into :&mac_var separated by "&dlm."

     from &dsn;

     quit;

     %let &mac_var = &&&mac_var;

     %put &mac_var = &&&mac_var;

%mend put_distinct_varvalues_in_list;

This macro calls will build list of values from a dataset as shown in comments below.

%put_distinct_varvalues_in_list(sashelp.class,sex,gender);  /* output gender = "F","M" */

%put_distinct_varvalues_in_list(sashelp.class,sex,gender,quotes=single); /* output gender = 'F','M' */

%put_distinct_varvalues_in_list(sashelp.class,sex,gender,dlm=comma,quotes=none); /* output gender = F,M */

%put_distinct_varvalues_in_list(sashelp.class,sex,gender,dlm=pipe,quotes=none); /* output gender = F|M */

%put_distinct_varvalues_in_list(sashelp.class,sex,gender,dlm=space,quotes=none); /* output gender = F M */

%put_distinct_varvalues_in_list(sashelp.class,sex,gender,dlm=space,quotes=double); /* output gender = "F" "M" */