proc sql;
select distinct Char_flag, num_flag into :charvars,:numvars separated by ","
from have
where Char_flag ne '' or num_flag ne '';
quit;
%put &charvars;
%put &numvars;
Macro variable numvars has no values in it. So when I pass in it in the below sql code I'm getting an error. How to pass it conditionally only if it has values in it?
proc sql;
create table want as
select &charvars
&numvars
from have
;
quit;
check whether you have zero records and then only execute. something like this
data abc; a=2; b=3; run; %macro abc; proc sql noprint; select count(*) into :cnt from (select distinct * from abc where a ne 2)a; /*try with eq 2*/ quit; %if %sysevalf(&cnt) = 0 %then %do; %put "no values are there"; %end; %else %do; proc sql; create table want as select distinct * from abc ; quit; %end; %mend; %abc;
Perhaps I am misuderstanding the question as it is a little confusing, but I think your issue is how to generate a select statement in SQL when sometimes the list of variables is empty. Is that right?
The easist way is to use normal SAS code that uses space delimited lists of variables instead of PROC SQL code that requires all of those stupid commas then the empty lists don't cause syntax errors.
%let requried=A B;
%let optional=;
data want ;
set have ;
keep &required &optional;
run;
Another trick I like to do is build the list as space delimited list and then convert to comma delimited list.
%let required=A B;
%let optional=;
%let varlist=&required &optional;
%let sqllist=%sysfunc(tranwrd(&varlist,%str( ),%str(,)));
proc sql;
create table want as
select &sqllist
from have
;
quit;
Are your variables Char_flag and Num_flag in the set Have actually the names of variables?
Do you need the separate charvars and numvars for something else later?
The first thing I would do would be pull the lists separately:
proc sql;
select distinct Char_flag into :charvars separated by ","
from have
where not missing( Char_flag) ;
select distinct num_flag into :numvars separated by ","
from have
where not missing( num_flag);
quit;
your compound requirement with the OR and Distinct may not be getting what you want to begin with.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.