Looping through variables

[PaigeMiller]

Please see the code above. It is one of the last pieces I have a problem with as it is in proc sql and loops generally might be created in data step, right?

You use the word "right" and follow it with a question mark, but I just don't understand what question is.

What I want is to replace those 40ish lines with something like that 

%do  i =  1 %to &num_obs;

so I would not have to amend the conditions if &num_obs will change.

Again, it's not clear to me what the question is.

[jepafob]

Sorry.

1. Is it possible to have loop inside where condition in proc sql?

2. 

proc sql;
create table vol_val_sum as select
count(ID) as vol_aff,
sum(amount) as val_aff,
count(distinct number) as vol_a
from test
where status='Con' and(
(&col1. ='Y' and &col1 in (select mnemonic from score where score=71)) or 
(&col2. ='Y' and &col2 in (select mnemonic from score where score=71)) or 
(&col3. ='Y' and &col3 in (select mnemonic from score where score=71)) or 
(&col4. ='Y' and &col4 in (select mnemonic from score where score=71)) or 
(&col5. ='Y' and &col5 in (select mnemonic from score where score=71)) or
…
(&col44. ='Y' and &col44 in (select mnemonic from score where score=71))
);
quit;

I want to replace the lines in where statement from &col1 to &col44 with some loop as sometimes I might have more or less than 44 variables and right now I have to remove or add new conditions to meet number of variables.

[PaigeMiller]

1. Macro looping is a TEXT processor, it can work inside PROC SQL or in a DATA step or in any other PROC or even outside of PROCs/outside of DATA steps, as long as it generates appropriate code. Macros can generate text anywhere in your SAS program. So if you write a macro that works inside a DATA step, that macro probably will not work inside of PROC SQL, as the code generated by the macro is not valid PROC SQL code, even if it is valid DATA step code. But you could certainly create a macro that works properly inside PROC SQL.
2. How would the programmer know how many &COL variables there are?

[jepafob]

2. It is based on the number of rows in one table. I count the rows of that table and saving it to the variable &num_obs.,

which I use later in every loop as a indicator of maximum number of iterations.

[PaigeMiller]

The code you presented with 44 cases works on variables, not observations as you are now stating. So its not clear how &NUM_OBS would apply to your SQL code.

[jepafob]

I am counting rows to find out the number of rows but later I transpose this table so col1-col40 will become variables as you can see in that example in the previous post. And from that point I use it as a variables not observations. Num_obs is just the name for total number of initially obs and  then variables that have been created.

To simplify let's say there is fixed amount of 40 variables, is it a way instead of having 40 lines like that (one line for every variable),

(&col1. ='Y' and &col1 in (select mnemonic from score where score=71)) or 

 to shorten it with a loop? and have something like that (in pseudo code)

do  i =  1 to 40;
(&col(i). ='Y' and &col(i) in (select mnemonic from score where score=71))

  where i is loop counter while everything is still in proc sql in where statement.

[Tom]

Why not just switch to using a data step so you can use an array?

If you variable names follow a simple pattern you can just hard code the list.

Or put the names into a macro variable and use that to generate the array statement.

[PaigeMiller]

---

@jepafob wrote:

To simplify let's say there is fixed amount of 40 variables, is it a way instead of having 40 lines like that (one line for every variable),

(&col1. ='Y' and &col1 in (select mnemonic from score where score=71)) or 

 to shorten it with a loop? and have something like that (in pseudo code)

do  i =  1 to 40;
(&col(i). ='Y' and &col(i) in (select mnemonic from score where score=71))

  where i is loop counter while everything is still in proc sql in where statement.

---

How about

%do i=1 %to 40;
(&&col&i = 'Y' and &&col&i in (select mnemonic from score where score=71))
%if &i<40 %then %str(or);
%end;

[jepafob]

@PaigeMillerhm..I don't entirely understand what you wrote. You meant something like that?

proc sql;
create table vol_val_sum as select
count(ID) as vol_aff,
sum(amount) as val_aff,
count(distinct number) as vol_a
from test
where status='Con' and(
%do i=1 %to 40;
(&&col&i = 'Y' and &&col&i in (select mnemonic from score where score=71))
%if &i<40 %then %str(or);
%end;
);
quit;

So the piece you wrote just producing the text that should be interpreted by proc sql as a part of where statement, do I understand it correctly?

@TomI don't understand what you mean by 'hard code the list'. There will be different volume of those variables every time I will run the program and under every variable there will be different value for every time I run the program.

[PaigeMiller]

Looks good to me, except that the entire PROC SQL ought to be inside a macro.

A %DO loop only works inside a macro.

[jepafob]

2653  %macro vol_val_sum2;
2654  proc sql;
2655  create table vol_val_sum2 as select
2656  count(ID) as vol_aff,
2657  sum(amount) as val_aff,
2658  count(distinct number) as vol_a
2659  from rule
2660  where status='Con' and(
2661  %do i=1 %to 5;
2662  (&&col&i = 'Y' and &&col&i in (select mnemonic from score where score=71))
2663  %if &i<5 %then %str(or);
2664  %end;
2665  );
2666  quit;
2667  %mend vol_val_sum2;
2668  %vol_val_sum2
MLOGIC(vol_val_sum2):  Beginning execution.
MPRINT(vol_val_sum2):   proc sql;
MLOGIC(vol_val_sum2):  %DO loop beginning; index variable I; start value is 1; stop
      value is 5; by value is 1.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable COL1 resolves to DEVICE1
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable COL1 resolves to DEVICE1
SYMBOLGEN:  Macro variable I resolves to 1
MLOGIC(vol_val_sum2):  %IF condition &i<5 is TRUE
MLOGIC(vol_val_sum2):  %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable COL2 resolves to DEVICE2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable COL2 resolves to DEVICE2
SYMBOLGEN:  Macro variable I resolves to 2
MLOGIC(vol_val_sum2):  %IF condition &i<5 is TRUE
MLOGIC(vol_val_sum2):  %DO loop index variable I is now 3; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable COL3 resolves to DEVICE3
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable COL3 resolves to DEVICE3
SYMBOLGEN:  Macro variable I resolves to 3
MLOGIC(vol_val_sum2):  %IF condition &i<5 is TRUE
MLOGIC(vol_val_sum2):  %DO loop index variable I is now 4; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable COL4 resolves to DEVICES
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable COL4 resolves to DEVICES
SYMBOLGEN:  Macro variable I resolves to 4
MLOGIC(vol_val_sum2):  %IF condition &i<5 is TRUE
MLOGIC(vol_val_sum2):  %DO loop index variable I is now 5; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable COL5 resolves to BANGLADESH
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable COL5 resolves to BANGLADESH
SYMBOLGEN:  Macro variable I resolves to 5
MLOGIC(vol_val_sum2):  %IF condition &i<5 is FALSE
MLOGIC(vol_val_sum2):  %DO loop index variable I is now 6; loop will not iterate
      again.
MPRINT(vol_val_sum2):   create table vol_val_sum2 as select
count(ID) as vol_aff, sum(amount) as val_aff, count(distinct
number) as vol_a from rule where status='Con' and( (DEVICE1 =
'Y' and DEVICE1 in (select mnemonic from score where score=71)) or
(DEVICE2 = 'Y' and DEVICE2 in (select mnemonic from score where
score=71)) or (DEVICE3 = 'Y' and DEVICE3 in (select mnemonic from score
where score=71)) or (DEVICES = 'Y' and DEVICES in (select mnemonic from
score where score=71)) or (BANGLADESH = 'Y' and BANGLADESH in (select mnemonic from
score where score=71)) );
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
MPRINT(vol_val_sum2):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.06 seconds

MLOGIC(vol_val_sum2):  Ending execution.

That's what I got. It is resolving the string correctly though.

[PaigeMiller]

So, once the macro resolves, you get text which must be legal valid working SAS code. In your case, you do not have text which is legal valid working SAS code. 

Here's examples of the text that is produced by your macro

DEVICE2 = 'Y' and DEVICE2 in (select mnemonic from score where score=71)

BANGLADESH = 'Y' and BANGLADESH in (select mnemonic from score where score=71)

and something about this is not going to work. Do you see why this won't work in PROC SQL and produces the error message shown? 

Many of these issues can be simplified or eliminated by first obtaining working code in SAS and you do this BEFORE you try to convert it to a macro. If you can get the code to work without macros, then getting it to work with macros shouldn't be that hard. If the code doesn't work without macro, then it will not work with macros.

[Tom]

I don't see where you have described your data or provided any example inputs and desired outputs.

If the names follow a pattern.  Like VAR1, VAR2, .... VAR203 then you can use a variable list, like var1-var203.

Also if you restructure your data so that you have the names in the value of a variable instead as the name of the variable then the code does not change when the data changes.

So if you have a dataset with multiple variables, say  : 

ID,DEVICE1,DEVICE2,BANGLADESH  
1,Y,N,Y
2,N,Y,Y

You can transpose it to instead have multiple observations:

ID,NAME,VALUE
1,DEVICE1,Y
1,DEVICE2,N
1,BANGLADESH,Y
2,DEVICE1,N
2,DEVICE2,Y
2,BANGLADESH,N

Now you have hundreds of values of the new NAME variable without it causing any change to the code you use to reference it.