topic Re: DOSUBL inside PROC SQL? in SAS Programming

DOSUBL inside PROC SQL?

PaigeMiller — Thu, 22 Dec 2022 14:20:01 GMT

Every morning when I wake up, I ask myself "How can I use DOSUBL today?" Okay ... not every morning ... But yesterday when I got to work, a colleague asked me if I could help him solve a problem. He has some code that joins several SAS data sets, and sometimes one variable doesn't exist in a data set, and then the join fails. I know the best solution is to figure out why this happens and then prevent it from happening further ... but it wasn't really my problem. My solution would be to add this missing numeric variable to a data set IF it is not already present, so that the next join would work. I know I could write a macro solution to this, but I decided to try a DOSUBL solution. This post isn't really a question ... but rather the result of this learning experience where I write some DOSUBL code that works in SQL, and maybe this solution is overcomplicated (as I said, I could have simply written a small macro without DOSUBL), but any suggestions are welcome.

Here is what I came up with.

%macro varexist(data=_LAST_,varname=,alias=);
    /* Use in SQL to add a column named &varname (which is numeric) if it doesn't already exist in the data set */
    /* If it does exist in the data set, just extract all existing variables */
    %if %upcase(%superq(data)) = _LAST_ %then %let data = &SYSLAST;
    %if &alias= %then %let word=_name_; 
    %else %let word=cats("&alias..",_name_); 
    %let rc = 
        %sysfunc(dosubl(%str(
            proc transpose data=&DATA(obs=0) out=ExpandVarList_temp;
                var _all_;
            run;
            proc sql noprint;
                select &word
                    into :temp_varnames separated by "," from ExpandVarList_temp ;
                drop table ExpandVarList_temp;
            quit; 
        )));
    %let varexist = %sysfunc(findw(%qupcase(&temp_varnames),%upcase(&varname),%str(,.)));
    %put &=varexist;
    %if not &varexist %then %let temp_varnames = %unquote(&temp_varnames%str(,. as )&varname);
    &temp_varnames
%mend varexist;

Usage:

proc sql;
    create table want as select %varexist(data=sashelp.class,varname=height)
    from sashelp.class;
quit;

Usage 2:

proc sql;
    create table want as select %varexist(data=sashelp.class,varname=bloodpressure)
    from sashelp.class;
quit;

Usage 3 (in a join)

data iq;
    length name $ 8;
    name='Jane';
    iq=150;
    output;
    name='Janet';
    iq=144;
    output;
run;
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;

Re: DOSUBL inside PROC SQL?

Quentin — Thu, 22 Dec 2022 23:39:02 GMT

Hi @PaigeMiller,

I like it. I think DOSUBL is probably severely under-used. It brings new possibilities to function-style macros, like this. I do have some thoughts to share for the sake of discussion.

1. As a general rule, I try to declare all macro variables that are intended to be "internal" to a macro as %local so as to avoid naming collisions. So I would add:

%local word rc temp_varnames varexist ;

Note while this is usually just protection against the possibility that a macro variable exists in an outer scope, this is particularly important when using DOSUBL. If you run the macro as is, you will see that it creates temp_varnames as a global macro variable. This is a result of an unfortunate (IMHO) DOSUBL design decision. The macro variable temp_varnames is created as a global macro variable in the side session. When it is returned to the main session if the macro variable does not already exist as a local variable, it is created as a global variable. I think it would have been better for DOSUBL to create it as a local macro variable (assuming it did not already exist as a global macro variable). Creating the variable as %local before calling DOSUBL allows DOSUBL to return the the macro variable to the local symbol table.

2. I think as a general practice it's better to use

%sysfunc(dosubl(%nrstr(

as a wrapper for the DOSUBL block rather than

%sysfunc(dosubl(%str(

Using %NRSTR() delays resolution of macro references until the code executes in the DOSUBL side-session. It's important to use %NRSTR() when the side-session code will use SAS code to create new macro variables that are referenced inside the DOSUBL block. The same way that it's recommended to use %NRSTR() inside CALL EXECUTE to delay resolution of macro variables. It's also important to use %NRSTR() if the code in the DOSUBL block has any macro statements, because you want them to execute in the side session. In this case, using %STR() works fine. But since %NRSTR() always works, I think it's a better choice.

3. I feel like %VarExist is an odd name for this macro. I'm not sure what I would name this macro. It's almost %ListVariablesAndAddANewVariableIfItDoesNotExist. If I see a macro named %VarExist, I think it will return 1/0 as to whether or not a variable exists.

4. Thinking about %VarExist made me wander down a different design path, still using DOSUBL. Suppose you take the core of your macro and turn it into a macro %VarList, which returns a list of variables in a dataset:

%macro varlist(data,dlm=%str( ));
    %local rc varlist ;
    %let rc = 
        %sysfunc(dosubl(%nrstr(
            proc contents data=&DATA out=ExpandVarList_temp(keep=name) noprint;
            run;
            proc sql noprint;
                select name
                    into :varlist separated by "&dlm" from ExpandVarList_temp ;
                drop table ExpandVarList_temp;
            quit; 
        )));
    &varlist
%mend varlist;

I think that is a really useful utility macro. You can do stuff like:

%put %varlist(data=sashelp.class) ;
%put %varlist(data=sashelp.class(keep=_numeric_)) ;
%put %varlist(data=sashelp.class(keep=name--age)) ;

That macro makes it easy to write a %VarExist macro which will tell you if a variable exists or not, e.g.:

%macro varexist(data=,var=);
  %*function-style macro, return 1 if variable exists, else 0 ;
  %eval(%sysfunc(findw(%qupcase(%varlist(&data,dlm=|)),%upcase(&var),|))>0)
%mend varexist;

With a %VarExist macro utility like that, I might stop there, and use an open %IF statement in my PROC SQL, e.g.:

proc sql;
    create table want as select *
      %if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
        , . as bloodpressure
      %end ;
    from sashelp.class;
quit;

For the join:

data iq;
    length name $ 8;
    name='Jane';
    iq=150;
    output;
    name='Janet';
    iq=144;
    output;
run;

proc sql;
    create table want as select 
      a.*
      %if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
        , . as bloodpressure
      %end ;
      ,b.iq
    from sashelp.class as a left join iq as b 
    on a.name=b.name;
quit;

Because %varlist returns a list of variables, you can use other list-processing macros on the list. For example, suppose you want to do the join, but want to keep only the character variables from sashelp.class. You can use Richard DeVenezia's %seplist (https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist) to add the a. alias to the list of character variables, e.g.:

proc sql;
    create table want as select 
      %seplist(%varlist(sashelp.class(keep=_character_)),prefix=a.,dlm=%str(,))
      %if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
        , . as bloodpressure
      %end ;
      ,b.iq
    from sashelp.class as a left join iq as b 
    on a.name=b.name;
quit;

Re: DOSUBL inside PROC SQL?

PaigeMiller — Fri, 23 Dec 2022 12:13:39 GMT

All great points, @Quentin .Thanks. As this was my first attempt to use DOSUBL, I can see that there's a lot more to think about.

Re: DOSUBL inside PROC SQL?

ErikLund_Jensen — Fri, 23 Dec 2022 12:50:14 GMT

Hi @PaigeMiller

"Every morning when I wake up, I ask myself "How can I use DOSUBL today?" - One should ask oneself questions like that whenever a new release of SAS brings new possibilities, but in a busy worklife, one tends to stick with old habits, and I am still trying to get the Hash Object into my personal tool box. I made a few experiments only with dosubl, so I am not qualified to discuss the details in using dosubl, but you really got me interested in learning more!

I have some comments on the technique you use to solve your collegaue's problem. It looks like overkill, but what you do is necessary some way or other, so I gave it a try to see if I could come up with a solution that seems simpler, and to get it to work with dusubl in the same way as your code (part of my learning).

The idea is to use dosubl to create a view with the missing numeric variable included if necessary instead of generating a variable select list, and - in following steps - to move the macro call from the variable list to the set/from definition, so the data is read through the created view. The macro returns the view name, which in my example is a text constant, because the same view name is reused in every call to the macro.

Don't let my writing the whole dosubl code in one line bother you. It did bother me, but I could'nt get the text coloration in Display Manager right if the included code is split in a line per statement as in your example. As is, my code leaves the last created view in SAS WORK. Not nice either, but as it is a view only and don't use space, I think it's a minor problem.

* macro to create view with a missing numeric variable added if necessary;
%macro thisview(table=,checkvar=);
  %let rc = %sysfunc(dosubl(%nrstr('data thisview / view=thisview; set &table; if missing(vnamex("&checkvar")) then &checkvar = .; run;')));
  thisview
%mend;

*Usage 1 (variable present):;
data test1; set %thisview(table=sashelp.class, checkvar=height);
run; 

*Usage 2 (variable missing):;
data test2; set %thisview(table=sashelp.class, checkvar=bloodpressure);
run; 

*Usage 3 (in a join);
data iq;
    length name $ 8;
    name='Jane'; iq=150; output;
    name='Janet'; iq=144; output;
run;

proc sql;
    create table test3 as select 
        a.*
        ,b.iq
    from %thisview(table=sashelp.class, checkvar=bloodpressure) as a left join iq as b 
    on a.name=b.name;
quit;

Re: DOSUBL inside PROC SQL?

PaigeMiller — Fri, 23 Dec 2022 12:56:21 GMT

I too am slowly trying to get Hash into my toolbox.

Thank you for the suggestions, should I ever need to use DOSUBL again, I will keeep this in mind.

So thanks @Quentin and @ErikLund_Jensen for having a discussion with me on thus rather arcane but promising topic.

Re: DOSUBL inside PROC SQL?

Quentin — Fri, 23 Dec 2022 13:58:32 GMT

Very nice, @ErikLund_Jensen . Your macro %thisview reminds me of Mike Rhoads's macro %GetSQL in the original macro-function-sandwich paper (precursor to DOSUBL). That macro also returned a view which could be used inline, like:

data example;
     set %GetSQL
       (select name, age, weight from sashelp.class where sex = 'M' order by age)
         %GetSQL
       (select name, age, height from sashelp.class where sex = 'F' order by name)
     ;
run;

That paper was also the original source of %ExpandVarList (using the transpose method shared by @data_null__ on SAS-L).

Just for the sake of future readers of this thread, my recommended readings on DOSUBL would be:

1. Mike Rhoads's MFS paper, now (gasp) 10 years old: https://support.sas.com/resources/papers/proceedings12/004-2012.pdf . It doesn't use DOSUBL, because it didn't exist at the time, but it introduces the idea of a function-style macro that can execute SAS code. I still remember where I was when I read this paper the first time; it was like Mike had invented magic. I couldn't believe it.

2. Rick Langston's paper where he introduced DOSUBL to the world: https://support.sas.com/resources/papers/proceedings13/032-2013.pdf. It ends withs a DOSUBL version of %ExpandVarList.

3. John King's paper presenting a DOSUBL version of ExpandVarList that has enhanced features for selecting the variables to be returned. https://www.mwsug.org/proceedings/2017/BB/MWSUG-2017-BB142.pdf

4. For those interested in how DOSUBL manages macro variable scopes, I tried to deduce the rules and present my understanding of them in this paper: https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4958-2020.pdf

I hope to see more papers exploring and documenting DOSUBL. There is much to be explored. For example, it's important to know which system options defined in the main session are inherited by the side-session, and which system options changed in the side-session will cause an update to the main session options.

Re: DOSUBL inside PROC SQL?

Quentin — Fri, 23 Dec 2022 14:28:50 GMT

As evidence of the need for more DOSUBL papers / blog posts, I tried asking ChatGPT about DOSUBL, and it seems to have it confused with the %include statement. : )

/* Main SAS program */

DOSUBL 'C:\Program Files\SAS\subroutine.sas';

/* Rest of main SAS program */

Asking it more questions led to humorous explanations, e.g.:

In this program, the SYMBOLGEN option is enabled, which creates a symbol table containing the values of the macro variables var1 and var2. The subroutine program can then access the values of these variables using the SYMGET function, as shown in the following example...

I guess there's a reason that stack overflow banned ChatGPT-generated answers : )

Re: DOSUBL inside PROC SQL?

Tom — Fri, 23 Dec 2022 15:42:14 GMT

This looks more reasonable. But it is simpler to just use LENGTH instead of an assignment statement to force the existence of the variable. That also means it can support multiple required variables in a single call.

%macro thisview(table=,required=);
%local rc;
%let rc = %sysfunc(dosubl(%nrstr('data thisview / view=thisview; set &table; length &required 8; run;')));
thisview
%mend;

As to the original problem of testing for the existence of a variable in a dataset there is no need to use DOSUBL as you can just use the VARNUM function. For example see this 24 year old macro: %varexist()

Re: DOSUBL inside PROC SQL?

Quentin — Fri, 23 Dec 2022 15:55:56 GMT

As to the original problem of testing for the existence of a variable in a dataset there is no need to use DOSUBL as you can just use the VARNUM function. For example see this 24 year old macro: %varexist()

Indeed, but I've got a similar SCL-function based version of %Varlist, and it's a hideous monster, particularly to support keep/drop. The DOSUBL version is actually readable (while of course executing MUCH more slowly, because of overhead of DOSUBL is substantial).

Re: DOSUBL inside PROC SQL?

Tom — Fri, 23 Dec 2022 16:34:08 GMT

@Quentin wrote:

As to the original problem of testing for the existence of a variable in a dataset there is no need to use DOSUBL as you can just use the VARNUM function. For example see this 24 year old macro: %varexist()

Indeed, but I've got a similar SCL-function based version of %Varlist, and it's a hideous monster, particularly to support keep/drop. The DOSUBL version is actually readable (while of course executing MUCH more slowly, because of overhead of DOSUBL is substantial).

In general yes.

Not in this case however. The pure SAS code you posted to test if the variable exists is 9 lines of code. And then you added all of the extra macro logic and DOSUBL() logic on top of that. The macro code in the VAREXIST macro is 9 lines of macro code, and it adds the functionality of returning the result of VARTYPE() or other VARxxx() functions if you want.

The SAS code you posted is using PROC CONTENT and PROC SQL. So to determine if a variable exists you get the whole list of variables, then have to try to test if the name is in the list.

The SCL functions (as you called them) is a better match to the actual problem.

Essentially the macro logic is:

Does the dataset exist? open() function call.

Does the variable exist? varnum() function call.

Re: DOSUBL inside PROC SQL?

PeterClemmensen — Sat, 24 Dec 2022 12:02:48 GMT

@Quentin, I'm not sure where, but I seem to remember reading that the Dosubl Function is a Behind The Scenes %Include Statement. Have your read that too or is the ChatGPT far off? 🙂

Re: DOSUBL inside PROC SQL?

yabwon — Sat, 24 Dec 2022 14:28:02 GMT

Quentin,

and here is the `SQLinDS` package: https://github.com/SASPAC/sqlinds

which is an implementation of Mike's paper and code with some additional extensions. Documentation is here: https://github.com/SASPAC/sqlinds/blob/main/sqlinds.md

Basic example:

data class;
  set %SQL(select * from sashelp.class order by age);
run;

prints the following in the log:

1      data class;
2          set %SQL(select * from sashelp.class order by age);
NOTE:*** the query ***
     "select * from sashelp.class order by age"
     *****************
*** executed as ***
>        select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height,
>CLASS.Weight
>          from SASHELP.CLASS
>      order by CLASS.Age asc;
>
*****************
3        run;

All the best

Bart

Re: DOSUBL inside PROC SQL?

yabwon — Sat, 24 Dec 2022 14:38:12 GMT

Rick is saying about this here:

https://blogs.sas.com/content/sasdummy/2018/12/20/five-sas-programming-language-features/

DoSubL discussion starts around 5:35

Bart

Re: DOSUBL inside PROC SQL?

ErikLund_Jensen — Sun, 25 Dec 2022 12:35:29 GMT

Hi @Tom

I did think of that, but dropped it, because

I always try to avoid the "NOTE: Variable <varname> is uninitialized." if it is foreseeable. This note often originates from missing variables in an input table, and in a production setup, it can be caught in an automated log scan and used for data quality control,
It has become a habit of mine never to reassign a length to a variable that exists in the PDV, because this in many situations throw the "WARNING: Multiple lengths were specified for the variable ...". It will not in this case, but old habits...

I got it to work with dosubl, because that is the topic of this thread, and it was a fun to play with. But for my own use, I wouldn't have written a function-like macro, I would have omitted the return value "thisview" from the macro and placed the macro call just before the step where it was needed, so the view was created and could be referred by name in a SQL from-clause or SET statement later.

Re: DOSUBL inside PROC SQL?

ChrisNZ — Mon, 24 Jun 2024 12:34:00 GMT

I just stumbled upon this nice idea.
The dosubl call can be shortened to

    %let rc=%sysfunc(dosubl(%nrstr(
       proc contents data=&data. out=___VAREXISTS___(where=(NAME=upcase("&varname"))) noprint; run;
       %let varexist = &sysnobs;
       proc delete data=___VAREXISTS___; run;
    )));

or even

%let rc=%sysfunc(dosubl(%nrstr(
  %let dsid     = %sysfunc(open(&data.));
  %let varexist = %sysfunc(varnum(&dsid.,&varname.));
  %let dsid     = %sysfunc(close(&dsid.));
)));

but then you don't need dosubl for this code.

Re: DOSUBL inside PROC SQL?

webart999ARM — Thu, 23 Jan 2025 22:22:38 GMT

%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

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.
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.
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.
Alias Handling
- Properly constructs aliases using cats("&alias..", name) to avoid syntax errors like a.name vs. a..name.
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.

Re: DOSUBL inside PROC SQL?

ChrisNZ — Tue, 28 Jan 2025 22:51:38 GMT

@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:


/* 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:

/*****************************************************************************
  
  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;