Process Metadata: List (from Fraktal SAS Programming)

Generate

The SQL procedure supports very nicely the creation of segmented parameter values from distinct values found for a specific variable in a data table. The target parameter name is indicated with a colon (":") and the concatenation of values is initiated with the separated by option and a by value (or string) in the select statement.

/* 
generate the list itself 
*/ 
proc sql noprint;
select distinct age
  into :age_list separated by ' , '
  from sashelp.class
;
quit;
%LET age_list = %QUOTE(&AGE_LIST.);

Be aware, that the SQL procedure is terminated with a QUIT not a RUN statement. To comply with other SQL interpreters, RUN statements do not exist in PROC SQL syntax. Instead, the semicolon (";") triggers processing of SQL statements instantly making each its own RUN GROUP.

Count

An automatic MACRO variable is used to get the next piece of information required. SQLOBS contains the number of loops necessary to read the lines of the input table, hence the number of elements that were written to the list.

To save the value it is written to a user owned variable with a LET statement.

/*
obtain the number of elements
*/
%LET age_grps = &SQLOBS.;

Utilize

With all the values in one segmented variable we now need to address each value by position. This is easily accomplished with the SCAN function inside a loop that moves over positions from "1" to the "number of elements" that was copied from system owned parameter SQLOBS to user owned parameter AGE_GRPS.

/*
utilize list elements 
*/
%DO age_indx = 1 %TO &AGE_GRPS.;
proc print noobs 
     data = sashelp.class
;
where age = %SCAN(&AGE_LIST.,&AGE_INDX.,',');
run;
%END;

It might appear obvious now why it was necessary to QUOTE the segmented AGE_LIST parameter when it was list-structured with commas:

The MACRO Facility’s pre-processor property causes replacement ("resolution") of the parameter before the function is executed which is likely to deliver more arguments than expected to the SCAN function.

Quoting inside the SAS System is very much like a science on its own and will be revisited whenever appropriate.