topic Re: proc sql code help in SAS Programming

proc sql code help

knveraraju91 — Sun, 10 Jun 2018 04:44:43 GMT

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;

Re: proc sql code help

Patrick — Sun, 10 Jun 2018 05:03:12 GMT

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

Re: proc sql code help

PGStats — Sun, 10 Jun 2018 05:25:27 GMT

Or perhaps

%let flag= & flag='Y';

which would only add to the confusion.