06-23-2017 12:47 PM
select distinct Char_flag, num_flag into :charvars,:numvars separated by ","
where Char_flag ne '' or num_flag ne '';
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?
create table want as
06-23-2017 01:13 PM
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;
06-23-2017 01:17 PM
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;
06-23-2017 01:31 PM
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:
select distinct Char_flag into :charvars separated by ","
where not missing( Char_flag) ;
select distinct num_flag into :numvars separated by ","
where not missing( num_flag);
your compound requirement with the OR and Distinct may not be getting what you want to begin with.