DATA Step, Macro, Functions and more

Macro variable empty values

Reply
Super Contributor
Posts: 673

Macro variable empty values

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;

PROC Star
Posts: 325

Re: Macro variable empty values

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;
Super User
Super User
Posts: 7,039

Re: Macro variable empty values

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;

 

 

Super User
Posts: 11,343

Re: Macro variable empty values

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.

 

Ask a Question
Discussion stats
  • 3 replies
  • 147 views
  • 1 like
  • 4 in conversation