@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;
... View more
