How to integrate SAS macro if statement with PROC SQL select statement to create table-CodePudding

The goal is to use if statement to create multiple columns in output table. I am trying to avoid using case when, because in practice 8 or 9 outputs depends on one condition, using case when will increase the complexity of my code.

I am new to SAS, following is my code, but it is not working.

%macro DPAPRDT:
proc sql;
    execute(
        create table test as (
          %IF table2.A < table2.C and table2.A > table3.D 
             %then 
               %do 
               select table1.A,
                      table1.B,
                      table2.D,
                      table2.E,
                      table2.F,
              
             %else
               %do
              select  table1.A,
                      table1.B,
                      table3.D,
                      table3.E,
                      table3.F
                %end;
               from table1 
               join table2 on ... 
               join table3 on ... 
  
       WITH DATA PRIMARY INDEX (table1.A, table1.B) 
    

%mend DPAPRDT;

So far I have 3 error code as:

ERROR: Expected semicolon not found.  The macro will not be compiled.
ERROR: A dummy macro will be compiled.
ERROR: Expected %TO not found in %DO statement.

Without using Macros, the solution is:

proc sql;
    execute(
        create table test as (
               select table1.A,
                      table1.B,
                      case when table2.A < table2.C and table2.A > table3.D 
                           then table2.D else table3.D end as return1,
                      case when table2.A < table2.C and table2.A > table3.D 
                           then table2.E else table3.E end as return2,
                      case when table2.A < table2.C and table2.A .. 
                      case when table2.A < table2.C and table2.A .. 
                         . 
                         .
                         .
                     from table1 
                     join table2 on ... 
                     join table3 on ... 
               )WITH DATA PRIMARY INDEX (table1.A, table1.B) 
 end;

I have to use case when more than 10 time with the same condition

CodePudding user response：

So you show this example code:

case when table2.A < table2.C and table2.A > table3.D 
     then table2.D 
     else table3.D 
end as return1

So turn those parts into macro variables:

case when (&var1 < &var2 and &var1 > &var3)
     then &var4
     else &var5 
end as &var6

Now you can convert that into a macro with 6 inputs.

%macro gen_case(var1,var2,var3,var4,var5,&var6)
case when (&var1 < &var2 and &var1 > &var3)
     then &var4
     else &var5 
end as &var6
%mend gen_case

Which you could then call multiple times to generate you multiple case clauses.

select table1.A
     , table1.B
     , %gen_case(table2.A,table2.C,table3.D,table2.D,table3.D,return1)
     , %gen_case(table2.A,table2.C,table3.D,table2.E,table3.E,return2)

Perhaps if the pattern is more repetitive than I can tell from your examples you could have the macro do even more. Possibly you could just pass in the list of datasets like: TABLE2 TABLE3 etc and the list of variables like: A C D E etc and have the macro put them together in different ways to produce the code you want.

CodePudding user response：

Please describe in more detail, and with some actual example data, what your overall goal is. Perhaps there isn't really any need to conditionally generate code at all.

For the macro processor to conditionally generate code you need to use conditions that it can test. For example if you have a macro variable named A and you want to test if the value of it is between the values of macro variables DATE_1 and DATE_2 then your %IF statement might look like:

%if (&date_1 <= &a) and (&a <= &date_2) %then %do;
  ... SAS Code you want the macro processor to generate ...
%end;
%else %do;
  ... Alternative SAS code ...
%end;

So what is the source of the value of A, DATE_1 and DATE_2?

Are they user defined?

%let a=100;
%let date_1=50;
%let date_2=200;

Are they derived from data? What data? How is it derived?