Macro with IF statement that sometimes is condition and sometimes is null

[Ronein]

Hello

I am not sure how to describe the question but I will try to explain.

I need to create multiple data sets .

The data sets differs from each other in 2 aspects:

1- City value (for example: London. Paris,Roma)

2-Existence of condition (IF statement). For some queries I need to use IF statement and for some not

Regarding aspect 1 (City) I know to create SAS parameter and use it

Regarding aspect 2 (IF statement),I don't know how to tell SAS to use it or not to use it.

May anyone help to do it in one macro?

note: paramater  t is just number of table that I create

%Macro RRR(City,t);
Data    Required&t.   ;
Set     raw_tbl(where=( group=”&Par1.”));
Y=min(x1,x2);
Run;
%mend;


%RRR(city=London,t=1, condition= IF Y>X3  then Y=X3/2;)
%RRR(city=London,t=2, condition= without condition;)
%RRR(city=Paris,t=3, condition= IF Y>X3  then Y=X3/2;)
%RRR(city= Paris,t=4, condition= without condition;)
%RRR(city=Roma,t=5, condition= IF Y>X3  then Y=X3/2;)
%RRR(city= Roma,t=6, condition= without condition;)

[Ronein]

The target is to create these 6 tables  by creating one Macro

Data    Required1;
Set     raw_tbl(where=( group=’ London’));
Y=min(x1,x2);
IF Y>X3  then Y=X3/2;
Run;

Data    Required2;
Set     raw_tbl(where=(group=’ London’));
Y=min(x1,x2);
Run;
 
Data    Required3;
Set     raw_tbl(where=( group=’ Paris’));
Y=min(x1,x2);
IF Y>X3  then Y=X3/2;
Run;

Data    Required4;
Set     raw_tbl(where=(group=’ Paris’));
Y=min(x1,x2);
Run;

 

Data    Required5;
Set     raw_tbl(where=( group=’ Roma’));
Y=min(x1,x2);
IF Y>X3  then Y=X3/2;
Run;

Data    Required6;
Set     raw_tbl(where=(group=’ Roma’));
Y=min(x1,x2);
Run;

 

[PaigeMiller]

By the way

You see these "curly" quotes in your code:

where=( group=’ London’)

These will not be recognized by SAS if you copy and paste into SAS, and they indicate that your code took a trip through Microsoft Word or PowerPoint or some other program that recognizes curly quotes. DO NOT PASTE YOUR CODE into these applications. It will only cause grief, because SAS will not recognize them. Only use true text editors when working with SAS code.

Furthermore, don't put unneeded spaces in there, as group='London' is not the same as group=' London'.

[PaigeMiller · Accepted solution]

/*** UNTESTED CODE ***/
%Macro RRR(City,t,condition);
    data Required&t.   ;
         set raw_tbl(where=(group=”&city”));
         Y=min(x1,x2);
         %if &condition ^= %then &condition;
    run;
%mend;
%RRR(city=London,t=1, condition=%str(IF Y>X3  then Y=X3/2;))
%RRR(city=London,t=2, condition=)
%RRR(city=Paris,t=3, condition=%str(IF Y>X3  then Y=X3/2;))
%RRR(city=Paris,t=4, condition=)
%RRR(city=Roma,t=5, condition=%str(IF Y>X3  then Y=X3/2;))
%RRR(city=Roma,t=6, condition=)

Special characters in the condition variable (the greater than sign, the equal sign and the division sign and the semi-colon) must be masked if they are going to be transmitted to your macro. Otherwise, you get errors.

[Ronein]

May I ask please how to write it when have more than one IF statement??

 Data    Required1;
Set     raw_tbl(where=( group=’ London’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required2;
Set     raw_tbl(where=(group=’ London’));
Y=min(x1,x2);
Run;
 
Data    Required3;
Set     raw_tbl(where=( group=’ Paris’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required4;
Set     raw_tbl(where=(group=’ Paris’));
Y=min(x1,x2);
Run;

 

Data    Required5;
Set     raw_tbl(where=( group=’ Roma’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required6;
Set     raw_tbl(where=(group=’ Roma’));
Y=min(x1,x2);
Run;

[PaigeMiller]

You put both IF statements in &CONDITION

[ballardw]

---

@Ronein wrote:

May I ask please how to write it when have more than one IF statement??

 Data    Required1;
Set     raw_tbl(where=( group=’ London’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required2;
Set     raw_tbl(where=(group=’ London’));
Y=min(x1,x2);
Run;
 
Data    Required3;
Set     raw_tbl(where=( group=’ Paris’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required4;
Set     raw_tbl(where=(group=’ Paris’));
Y=min(x1,x2);
Run;

 

Data    Required5;
Set     raw_tbl(where=( group=’ Roma’));
Y=min(x1,x2);
IF Y>X3  AND Y<X4   then Y=X3/2;
IF Y>X3  AND Y>=X4   then Y=X3/3;
Run;

Data    Required6;
Set     raw_tbl(where=(group=’ Roma’));
Y=min(x1,x2);
Run;

---

If the conditional code is always exactly the same then perhaps you just need an indicator parameter.

%Macro RRR(City=,t=,option=Y);
    data Required&t.   ;
         set raw_tbl(where=(group="&city"));
         Y=min(x1,x2);
         %if &option=Y %then %do;
            IF Y>X3  AND Y<X4   then Y=X3/2;
            Else IF Y>X3  AND Y>=X4   then Y=X3/3;
         %end;
    run;
%mend;

%rrr(city=London,t=1);
%rrr(city=London,t=2,option=N);

Named parameters as used above have a couple of advantages. One is that you can set a default value. If you do not specifically set the value then it has the default. In this case I have set it to default to using those two extra lines of code. Any value other than Y and the two lines don't get executed.

If you have LOTS of different codes to execute conditionally then this may not be quite as useful.

Additionally it appears as if you might want to do this as pairs of data sets. Is that actually the case where you ALWAYS make two output sets for each city? If so then perhaps:

%Macro RRR(City,t);
    %let t2=%eval(t+1);
    data Required&t.  Required&t2. ;
         set raw_tbl(where=(group="&city"));
         Y=min(x1,x2);
         output Required&t.;
         IF Y>X3  AND Y<X4   then Y=X3/2;
         Else IF Y>X3  AND Y>=X4   then Y=X3/3;
         output Required&t2.;
    run;
%mend;