proc sql code help

[knveraraju91]

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;

[Patrick]

@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;

[PGStats]

Or perhaps

%let flag= & flag='Y';

which would only add to the confusion.