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;)
/*** 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.
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;
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'.
/*** 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.
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;
You put both IF statements in &CONDITION
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.