DATA Step, Macro, Functions and more

proc sql code help

Reply
Super Contributor
Posts: 317

proc sql code help

Dear,

 

The sql code appeared in a macro. I want to know why '1' is  in where statement. Please help. Thank you

%let flag=flag='Y'
proc sql; create table population_extract as select distinct adsl.studyid ,adsl.sitenum from adsl as a left join adex as b where 1 &flag ; quit;
Respected Advisor
Posts: 4,702

Re: proc sql code help

Posted in reply to knveraraju91

@knveraraju91

I can only guess but I assume this allows for the &FLAG macro variable to be empty. 

If the &flag variable is empty then the where clause will resolve to WHERE 1 which is always TRUE - the condition could also be WHERE 1=1 which maybe would be easier to understand.

You need some hard coded condition as a WHERE clause without anything would lead to a syntax error.

 

IF you populated the &FLAG macro variable then you need to use a string with results n a valid WHERE clause so something like below which then resolves to WHERE 1 AND flag='Y'

%let flag= AND flag='Y';
proc sql;
   create table population_extract  as
      select distinct
             adsl.studyid
            ,adsl.sitenum
                
      from adsl as a left join adex as b
      where	1
         	&flag
       ;
quit;
Esteemed Advisor
Posts: 5,487

Re: proc sql code help

Or perhaps

 

%let flag= & flag='Y';

which would only add to the confusion.

 

PG
Ask a Question
Discussion stats
  • 2 replies
  • 110 views
  • 2 likes
  • 3 in conversation