If statement is not working

[sas_user_null]

Hi all,

I have a question for using "if statement". I created a static list prompt. When selected, prompt outputs following variables: 

%LET Selected_Scenarios_count = 3;
%LET Selected_Scenarios = Base;
%LET Selected_Scenarios0 = 3;
%LET Selected_Scenarios3 = Down;
%LET Selected_Scenarios1 = Base;
%LET Selected_Scenarios2 = Up;

Here I share a sample piece of my code. I am using proc sql within sas macro. In the bold part I wish to calculate only for the Up and Down scenarios. However the code runs for all the scenarios. 

Can you help?

%macro MY_SAMPLE();

proc sql; create table WORK.MY_SAMPLE_TABLE as
select
Field1,
Field2,
%do s=1 %to &Selected_Scenarios_count.;
sum(Name_&&selected_scenarios&s..) as Name_&&selected_scenarios&s..,
if &&selected_scenarios&s.. in('Up', 'Down') then do;
sum(Name3_&&selected_scenarios&s.._Y&y.) as Name3_&&selected_scenarios&s..,
%end;
%end;
Field5
from WORK.MY_SOURCE
group by 1,2
;quit;

%mend MY_SAMPLE;
%MY_SAMPLE();

[Kurt_Bremser]

---

@sas_user_null wrote:

Hi all,

I have a question for using "if statement". I created a static list prompt. When selected, prompt outputs following variables: 

%LET Selected_Scenarios_count = 3;
%LET Selected_Scenarios = Base;
%LET Selected_Scenarios0 = 3;
%LET Selected_Scenarios3 = Down;
%LET Selected_Scenarios1 = Base;
%LET Selected_Scenarios2 = Up;

Here I share a sample piece of my code. I am using proc sql within sas macro. In the bold part I wish to calculate only for the Up and Down scenarios. However the code runs for all the scenarios. 

Can you help?

%macro MY_SAMPLE();

proc sql; create table WORK.MY_SAMPLE_TABLE as
select
Field1,
Field2,
%do s=1 %to &Selected_Scenarios_count.;
sum(Name_&&selected_scenarios&s..) as Name_&&selected_scenarios&s..,
if &&selected_scenarios&s.. in('Up', 'Down') then do;
sum(Name3_&&selected_scenarios&s.._Y&y.) as Name3_&&selected_scenarios&s..,
%end;
%end;
Field5
from WORK.MY_SOURCE
group by 1,2
;quit;

%mend MY_SAMPLE;
%MY_SAMPLE();

---

Your code won't run at all, as it has an unbalanced %end, so the macro won't even compile.

Start using proper code formatting, it helps in detecting such mistakes.

[ballardw]

Proc SQL does not have an IF. Use CASE WHEN

Maybe

case when  &&selected_scenarios&s. in('Up', 'Down') then  sum(Name3_&&selected_scenarios&s._Y&y.) 
  end as Name3_&&selected_scenarios&s.

Note that you have several times included too many . with the &&selected_scenarios&s. If you use

Name3_&&selected_scenarios&s.._Y&y then you get something like name3text._y95 which is not going to fly in SQL either.

Name3_&&selected_scenarios&s.. Will create a variable with a period as the last character also not valid.

[Tom]

When you use && in macro code then the macro processor will scan the token twice, consuming two of the periods.

[PaigeMiller]

Important first step: Create working legal valid SAS code without macros and without macro variables for one or two cases (for example, in this case, when s=1 and s=2). Once you have that working, you can attempt to turn it into a SAS macro. If you don't have it working without macros and without macro variables, it will never work when you try to turn it into working macro code.

[Tom · Accepted solution]

You need to use %IF not IF to conditionally generate code.  Also you need to tell SAS if you want the macro processor to treat IN as an operator.  Indenting your code will also make it easier to check that your logic makes sense.  Also it is much easier to scan and check multiple line statements if you place conjunctions, like the commas in SQL statements, at the beginning of the lines instead of the end of the lines.

So first you need to define the macro:

%macro MY_SAMPLE() /minoperator mindelimiter=' ';
%local s ;
proc sql;
create table WORK.MY_SAMPLE_TABLE as
  select
    Field1
   ,Field2
%do s=1 %to &Selected_Scenarios_count.;
   ,sum(Name_&&selected_scenarios&s..) as Name_&&selected_scenarios&s..
  %if &&selected_scenarios&s.. in (Up Down) %then %do;
   ,sum(Name3_&&selected_scenarios&s.._Y&y.) as Name3_&&selected_scenarios&s..
  %end;
%end;
   ,Field5
  from WORK.MY_SOURCE
  group by 1,2
;
quit;
%mend MY_SAMPLE;

Then you can set your macro variables and call the macro.  Make sure to set the macro variable Y that your code is using, but you didn't provide in your example code. You didn't provide an input dataset description, so I just made a dummy dataset with the variables needed to prevent SAS from throwing errors when trying to run the generated SQL query. Turn on the mprint option to see what statements it generates.  

%LET Selected_Scenarios_count = 3;
%LET Selected_Scenarios = Base;
%LET Selected_Scenarios0 = 3;
%LET Selected_Scenarios3 = Down;
%LET Selected_Scenarios1 = Base;
%LET Selected_Scenarios2 = Up;
%LET Y=2019;

options mprint;

data my_source;
  length field1 field2 field5 $1 name_base name_up name_down 
         Name3_Down_Y2019 Name3_Up_Y2019 8
  ;
run;

%MY_SAMPLE();

Results:

380   %MY_SAMPLE();
MPRINT(MY_SAMPLE):   proc sql;
MPRINT(MY_SAMPLE):   create table WORK.MY_SAMPLE_TABLE as select Field1 ,Field2 ,sum(Name_Base) as
Name_Base ,sum(Name_Up) as Name_Up ,sum(Name3_Up_Y2019) as Name3_Up ,sum(Name_Down) as Name_Down
,sum(Name3_Down_Y2019) as Name3_Down ,Field5 from WORK.MY_SOURCE group by 1,2 ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.MY_SAMPLE_TABLE created, with 1 rows and 8 columns.

MPRINT(MY_SAMPLE):   quit;

[sas_user_null]

Dear all,

Thank you for your answers but I still have the problem. I altered the code with case statement as shown below. However it still does the calculation for all the scenarios. Let me also mention that I also have another code for a similar purpose which runs within data step. It also does not do the filter. 

I hope someone can help. Thanks.

%macro MY_SAMPLE();
proc sql; create table WORK.MY_SAMPLE_TABLE as
	select 
Field1,
Field2,
	%do s=1 %to &Selected_Scenarios_count.;
		(case when &&selected_scenarios&s.. in ('Up', 'Down') then sum(Name_&&selected_scenarios&s..) end) as Name_&&selected_scenarios&s..,
	%end;		
Field4
		from WORK.MY_SOURCE
	group by 1,2
;quit;
%mend MY_SAMPLE;
%MY_SAMPLE();

[Kurt_Bremser]

Why do you think your code would filter for anything, when there is no where clause at all?

[sas_user_null]

I mean I want the code to only create the new fields for selected scenarios ('Up','Down'). However it runs for all.

[Kurt_Bremser]

Of course it will run for all, as the loop loops through all possible selections. If you want only the selected scenarion, you must use only that, and without a %do loop, you won't even need a macro.

Start by creating a working code for the selected scenarion, without using the prompt value. Once you have that, you will see how to insert the macro variable from the prompt.

[sas_user_null]

Please see this post for my question with data step sample.

https://communities.sas.com/t5/SAS-Programming/Cannot-filter-selection-in-prompt/td-p/614530

[PaigeMiller]

Again, you need to create valid legal working SAS code without macros and without macro variables for two cases (two iterations of your loop). If you don't have that, then your macros will never work.

[Tom]

I don't understand what you are trying to do. 

Your macro logic does not have any conditional logic to prevent if from generating one variable in the select list for each iteration of the %DO loop.

What are the names of your dataset variables?  Does you dataset even have variables named UP, DOWN or BASE? Are they character variables? Do any of them ever have values like 'Up' or 'Down'?