topic Re: Macro variable empty values in SAS Programming

Macro variable empty values

SASPhile — Fri, 23 Jun 2017 16:47:37 GMT

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;

Re: Macro variable empty values

kiranv_ — Fri, 23 Jun 2017 17:13:30 GMT

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;

Re: Macro variable empty values

Tom — Fri, 23 Jun 2017 17:17:19 GMT

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;

Re: Macro variable empty values

ballardw — Fri, 23 Jun 2017 17:31:27 GMT

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.