DOSUBL inside PROC SQL?

[webart999ARM]

%macro varexist(data=_LAST_, varname=, alias=);
    %local rc temp_ds temp_varnames varexist;
    %if %upcase(&data) = _LAST_ %then %let data = &SYSLAST;

    /* Use a unique temp dataset name to avoid collisions */
    %let temp_ds = __temp_%sysfunc(monotonic());

    /* DOSUBL block to check variable existence and generate varlist */
    %let rc = %sysfunc(dosubl(%nrstr(
        /* Check if dataset exists */
        %if %sysfunc(exist(&data)) %then %do;
            /* Get list of variables */
            data &temp_ds (keep=name);
                set &data (obs=0);
                array _num[*] _numeric_;
                do i = 1 to dim(_num);
                    name = vname(_num[i]);
                    output;
                end;
                stop;
            run;

            /* Check if &varname exists */
            data _null_;
                set &temp_ds end=eof;
                if upcase(name) = upcase("&varname") then do;
                    call symputx('varexist', 1, 'G');
                    stop;
                end;
                if eof and not symgetn('varexist') then call symputx('varexist', 0, 'G');
            run;

            /* Generate comma-separated varlist with alias */
            proc sql noprint;
                select 
                    %if &alias ne %then %do;
                        cats("&alias..", name)
                    %end;
                    %else %do;
                        name
                    %end;
                into :temp_varnames separated by ","
                from &temp_ds;
                drop table &temp_ds;
            quit;
        %end;
        %else %do;
            %put ERROR: Dataset &data does not exist.;
            %abort;
        %end;
    )));

    /* Append missing variable if needed */
    %if &varexist = 0 %then %do;
        %if &alias ne %then %let temp_varnames = &temp_varnames, . as &varname;
        %else %let temp_varnames = &temp_varnames, . as &varname;
    %end;

    /* Return the final varlist */
    &temp_varnames
%mend varexist;

Key Improvements

1. Avoid Global Scope Pollution

   • Uses %local for macro variables to prevent unintended interactions with the global environment.

   • Generates a unique temp dataset name (__temp_<monotonic()>) to avoid collisions in concurrent runs.

2. Efficiency

   • Replaces PROC TRANSPOSE with a lightweight data step using vname() to extract variable names. This is faster and avoids unnecessary transposition.

   • Uses array _num[*] _numeric_ to focus on numeric variables (aligns with your original problem statement). For character variables, add _char_ array logic.

3. Robustness

   • Explicitly checks if the dataset exists using %sysfunc(exist(&data)) before proceeding.

   • Uses call symputx with 'G' scope to ensure &varexist and &temp_varnames are available globally after DOSUBL.

4. Alias Handling

   • Properly constructs aliases using cats("&alias..", name) to avoid syntax errors like a.name vs. a..name.

5. Case Insensitivity

   • Uses upcase(name) = upcase("&varname") to handle case mismatches between dataset variables and user input.

/* Usage 1: Variable exists (no alias) */
proc sql;
    create table want as 
    select %varexist(data=sashelp.class, varname=height)
    from sashelp.class;
quit;

/* Usage 2: Variable does not exist (with alias) */
proc sql;
    create table want as 
    select %varexist(data=sashelp.class, varname=bloodpressure, alias=a)
    from sashelp.class as a;
quit;

/* Usage 3: Join with dynamic variable handling */
proc sql;
    create table want as 
    select %varexist(data=sashelp.class, varname=bloodpressure, alias=a), b.iq
    from sashelp.class as a 
    left join iq as b 
    on a.name = b.name;
quit;

I made some adjustments and hope that someone finds them helpful.

[ChrisNZ]

@webart999ARM 

---

Here's my take.

A more descriptive macro name , much less code (easier to understand and maintain) and more checks (input vetting, data set contains variables).

I don't care about unique temp dataset name or global macro variable as they are short-lived and not useful to me.

Macro:

Spoiler

/* Macro used in a SQL select statement that returns the variable names from a data set.        */
/* If column &varname doesn't exist, it's added to the list with a missing numeric value (dot). */   
/* If column &varname already exists in the data set, simply list the variable names.           */
 
%macro varaddsql(data=_LAST_, varname=, alias=);

  %*** INIT ***;
  %local rc dsvars addalias addvar;
  %let data   = %upcase(%sysfunc(compress(%superq(data   ),.,kn))); %* clean input  ; 
  %let varname= %upcase(%sysfunc(compress(%superq(varname), ,kn))); %* clean input  ;
  %let alias  = %upcase(%sysfunc(compress(%superq(alias  ), ,kn))); %* clean input  ;
  %if ^%sysfunc(exist(&data)) %then %do;                            %* vet input    ;
    %put ERROR: Dataset &data does not exist. Macro VARADDSQL will stop executing. ;
    %return;
  %end;
  
  %*** MAIN ***;  
  %if &data = _LAST_  %then %let data     = &syslast.     ;        %* fetch _last_ data set name               ;
  %if %length(&alias.) %then %let addalias = "&alias.." || ;       %* prep table alias to add to variable name ;
  %let rc = %sysfunc(dosubl(%nrstr( ; 
    proc contents data=&data noprint out=ZZ_VARADDSQL(keep=NAME);  %* get variable names                       ;
    run;
    proc sql noprint;  
      select &addalias. upcase(NAME)
        into :dsvars separated by ',' from ZZ_VARADDSQL;           %* create variable list                     ;
      drop table ZZ_VARADDSQL;                                     %* drop temp table                          ;
    quit; 
  ))); 
  %if ^%length(&dsvars.) %then %do;                                %* check var list                           ;
    %put ERROR: Dataset &data contains no variables. Macro VARADDSQL will stop executing. ;
    %return;
  %end;
  %if ^%sysfunc(findw( %quote(&dsvars), &varname, %str(,.) ))
    %then %let addvar= , . as &varname.;                           %* prep missing variable to add to var list ;
    
  %*** RETURN COMMA-DELIMITED LIST OF VARIABLES, INCLUDING MISSING VARIABLE IF NEEDED ***;  
  &dsvars. &addvar.
  
%mend varaddsql; 

Unit Test:

Spoiler

/*****************************************************************************
  
  Unit test for program varaddsql.sas 

******************************************************************************
Who           When       What
******************************************************************************
 ---
******************************************************************************/


%*************************************************************************;
%*** FAILURE expected ****************************************************;
%*************************************************************************;

* Invalid dataset name = > Message is displayed and error triggered.;
proc sql;
  create table WANT as
  select %varaddsql(data=SASHELP.CLASSX, varname=HEIGHT)
  from SASHELP.CLASS;
quit;

* No variables in data set = > Message is displayed and error triggered.;
data BAD;
run;
proc sql;
  create table WANT as
  select %varaddsql(data=BAD, varname=HEIGHT)
  from BAD;
quit;

%*************************************************************************;
%*** SUCCESS expected ****************************************************;
%*************************************************************************;

* Variable exists = > List of variables returned.;
proc sql;
  create table WANT1 as 
  select %varaddsql(data=SASHELP.CLASS, varname=HEIGHT)
  from SASHELP.CLASS;
quit;

* Variable exists, data set name stripped of invalid characters = > List of variables returned.;
proc sql;
  create table WANT1 as 
  select %varaddsql(data=SASHELP.CLASS@, varname=HEIGHT)
  from SASHELP.CLASS;
quit;

* Variable exists and _LAST_ data set used = > List of variables returned.;
proc sql;
  create table WANT2 as 
  select %varaddsql(data=_LAST_, varname=HEIGHT)
  from SASHELP.CLASS;
quit;

* Variable does not exist = > List of variables returned with added variable.;
proc sql;
  create table WANT3 as 
  select %varaddsql(data=SASHELP.CLASS, varname=BLOODPRESSURE)
  from SASHELP.CLASS;
quit;

* Variable does not exist and alias used = > List of variables returned with alias and with added variable.;
data IQ;
  NAME='Jane '; IQ=150; output;
  NAME='Janet'; IQ=144; output;
run;  
proc sql;
  create table WANT4 as 
  select %varaddsql(data=SASHELP.CLASS, varname=BLOODPRESSURE, alias=a)
        ,b.IQ
  from SASHELP.CLASS as a 
         left join 
       IQ            as b 
         on a.NAME=b.NAME;
quit;