0

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
Improve this question
9
  • Why do you think you need to make two different dataset ("table") structures? What is the actual test you want to use to determine which structure to make? Your current test will always be FALSE since the letter A is less than the letter B in the ASCII (and EBCDIC) character set. Commented Aug 9, 2022 at 17:09
  • Hi Tom, the my real condition is %IF A between date_1 and date_2. I want to pull data from different tables based on whether A fall into certain time frame. Commented Aug 9, 2022 at 17:15
  • If you want to conditionally generate code you need to do it based on values that the macro processor can test. So not the name of a variable but a single actual value. Also the macro processor does not understand BETWEEEN. You need to use two explicit range tests. %if (&MIN <= &A) and (&A <= &MAX) %then %do; ... actual sas code to generate goes here .. %end; Commented Aug 9, 2022 at 17:22
  • 1
    Still interested in understanding the actual problem. Perhaps there is a much easier way to do what you actually want rather then just generate the same complex code you are currently using. xyproblem.info Commented Aug 9, 2022 at 20:55
  • 1
    This may be better in a data step then rather than SQL. For the CASE WHEN, generating it by macro won't be much different because it needs to occur row by row unless those datasets only have a single entry so dynamic code generation doesn't quite make sense here. Commented Aug 9, 2022 at 21:35

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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?

Improve this answer

1 Comment

Hi Tom I edited my question based on your feedback. The challenge I am struggled is how to combine SAS macro with PROC SQL select statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.