List Manipulations Made Easy (and little bit of UGLY): the New DOSUBL Function

Typically, SAS list manipulations needs bunch of SAS I/O functions which are not necessarily well known to all SAS programmers. The new DOSUBL function makes this technique much more easier and little bit of ugly I must admit:

%MACRO ExpandVarList(data=LAST, var=ALL);
    %if %upcase(%superq(data)) = LAST
    %then %let data = &SYSLAST;
    %let rc = %sysfunc(dosubl(%str(
    proc transpose data=&DATA(obs=0) out=ExpandVarList_temp;
        var &VAR;
    run;
    proc sql noprint;
        select name into :temp_varnames separated by ‘ ‘
        from ExpandVarList_temp
        ;
    drop table ExpandVarList_temp;
    quit
    )));
    &temp_varnames
%MEND ExpandVarList;

%put %ExpandVarList(data=sashelp.class);

This piece of code comes from Submitting SAS Code On The Side by Rick Langston. It’s not elegant by any means compared to this version, but does have huge advantage of utilizing traditional SAS programming elements like data steps, procedures and such.

————UPDATE 2015-03-16—————————

Ronan Martorell, a SAS programmer from Paris, came out other two methods for the task above, also leveraged by DOSUBL (the sky is not limited!). The followings are his piece of codes (with permissions; Thank you Ronan!):

 /* — Method no. 2 :using a Data Step + Array launched by DOSUBL– */
%macro VarList(data=);
    %local varlist;

    /* DOSUBL required for executing the Data Step in a distinct session /
    %let rc = %sysfunc(dosubl(%str(
    Data null;
    length varlist $32167.;
        set &data(obs=1);
        array avarn[] NUMERIC;
        array avarc[*] CHARACTER;
        do i=1 to dim(avarn);
            varlist=CATX(‘ ‘,varlist, vname(avarn[i]));
        end;
        do i=1 to dim(avarc);
        *no self-reference allowed!;
            if vname(avarc[i]) NE ‘varlist’ then
                varlist=CATX(‘ ‘,varlist, vname(avarc[i]));
        end;
         call symputx(‘varlist’,varlist);
    run;
    )));
   
    &varlist
%mend VarList;
%put %VarList(data=sashelp.class);

/* — Method no. 3:  using Dictionary View COLUMNS launched by DOSUBL –*/
%macro VarlistDIC(data=);
    %local libname memname varlist;

    /* Test : if . separator exists in DataSet name then extracts Libname & Memname strings accordingly */
    %let libname=%UPCASE( %sysfunc( IFC( %index(&data, .) EQ 0, WORK, %scan(&data,
                1) )));
            %let memname=%UPCASE( %sysfunc( IFC( %index(&data, .) EQ 0, &data,
                %scan(&data, 2) )));
            %let rc = %sysfunc(dosubl(%str(

    proc sql noprint;
        select name into :varlist separated by ‘ ‘
        from DICTIONARY.COLUMNS
        where libname EQ “&libname” and memname EQ “&memname”
        ;
    quit;
    )));

    &varlist
%mend VarlistDIC;
%put %VarListDIC(data=sashelp.class);

Ronan also kindly contributed his I/O version (check out another I/O version in my github page):

/* — Method no. 4 : using SCL function with iterative FETCHOB + DICTIONARY VIEW — /
%macro ListVar(libname=, memname=);
    / local var initialisation /
    %let listvar=;
    %let i=0;      
    / open dictionary view with WHERE clause/
    %let id=%sysfunc(open(sashelp.vcolumn (where=( libname = “%upcase(&libname)” AND memname= “%upcase(&memname)“))));
    / load variable names into macro-variables (beware macro var collision !) /
    / Cf. http://www.notecolon.info/2013/01/note-open-function-reading-data-sets-in.html /
    %syscall set(id);
    %let rc=0;
    / loop into each line and accumulate the variable names into a list*/
    %do %until(&rc LT 0);
        %let i=&i+1;
        %let rc=%sysfunc(fetchobs(&id, &i));

        /* force loop exit once last variable has been stored into the list */
        %let listvar= %sysfunc( IFC ( &rc GE 0, &listvar &Name, &listvar ));
    %end;
    %let id=%sysfunc(close(&id));

   /* expands the list */
  &listvar
%mend;
%put %ListVar(libname=sashelp, memname=class);