topic Re: Looping through variables in SAS Programming

Looping through variables

jepafob — Thu, 21 May 2020 13:56:36 GMT

Hi,

I have a folowing problem:

I have the working code below. I want to improve and shorten it.

Have:

data test; set test;
if find(results,trim("&col1a.")) then &col1.='Y'; else &col1.='N';
if find(results,trim("&col2a.")) then &col2.='Y'; else &col2.='N';
if find(results,trim("&col3a.")) then &col3.='Y'; else &col3.='N';
if find(results,trim("&col4a.")) then &col4.='Y'; else &col4.='N';
if find(results,trim("&col5a.")) then &col5.='Y'; else &col5.='N';
if find(results,trim("&col6a.")) then &col6.='Y'; else &col6.='N';
if find(results,trim("&col7a.")) then &col7.='Y'; else &col7.='N';
if find(results,trim("&col8a.")) then &col8.='Y'; else &col8.='N';
if find(results,trim("&col9a.")) then &col9.='Y'; else &col9.='N';
if find(results,trim("&col10a."))then &col10.='Y'; else &col10.='N';

run;

Want:

I am looking to replace the above with a loop to iterate through n number of variables (&col(x).) following the same logic.

Examples of &col1 and &col1a variables:

2185 %put &col1;
someword
2186 %put &col1a;
someword=Y

String 'results' looks like that: results='someword=Y;someword2=N;someword3=N;' etc...

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 14:25:37 GMT

So you already have created macro variables &col1a through &col10a and &col1 to &col10?

UNTESTED CODE

%macro dothis;
data test1;
    set test;
    %do i=1 %to 10;
    if find(results,trim("&&col&i..a")) then &&col&i=1; else &&col&i=0;
    %end;
run;
%mend dothis;
%dothis

Please notice:

The code data test; set test; which overwrites TEST with a new TEST is not really a good approach unless you are 100% sure your code is working (and even then I usually avoid it), and thus in my first line I use data test1;

Also please notice:

My preference for binary variables is 0 and 1 instead on 'N' and 'Y'. This enables you to compute percentages with a simple mean calculation.

Re: Looping through variables

jepafob — Thu, 21 May 2020 14:50:34 GMT

Thank you, I will test it and let you know.

I was changing the names of the tables to more approachable. Originally it is not test and test. I put the same name by accident but thanks for pointing that out anyway 🙂

Yes I have two loops a step before this one, that assigning all the variables for me.
originally is col(around 40). It varies so I am using dynamic table there.
So in your code instead of 10, I will have to put variable that stores how many col(x) there is.

Re: Looping through variables

jepafob — Thu, 21 May 2020 15:07:02 GMT

OK so that's what I got.

2452 %macro dothis;
2453 data rule;
2454      set test;
2455      %do i=1 %to &num_obs;
2456      if find(results,trim("&&col&i..a")) then &&col&i=1; else &&col&i=0;
2457      %end;
2458 run;
2459 %mend dothis;
2460 %dothis
SYMBOLGEN: Macro variable NUM_OBS resolves to 44
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable COL1 resolves to someword

SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable COL1 resolves to someword2

…

and so on for all the variables up to 44.

So after 'then' part is working perfectly, just the argument of find function is not resolving properly.

Maybe it has something to do with this 'a' at the end. I could change it to be for example cal1 instead of col1a if that makes any difference.

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 15:11:39 GMT

options mprint symbolgen mlogic;

Use this command before your macro and then run it again and show us the whole LOG as it appears, without modification, without chopping stuff out, we need to see the code and the NOTES and WARNINGS and ERRORs for the part where Macro variable I resolves to 1 (We don't need when I resolves to >1)

Please, this is critically important, when you post the log you must preserved the formatting of the log to make sure that we can read and interpret it properly. Click on the </> icon and then paste the log into the window that appears. DO NOT SKIP THIS STEP.

Re: Looping through variables

jepafob — Fri, 22 May 2020 15:16:51 GMT

2472   options mprint symbolgen mlogic;
2473  %macro dothis;
2474  data rule;
2475      set test;
2476      %do i=1 %to 5;
2477      if find(results,trim("&&col&i..a")) then &&col&i=1; else &&col&i=0;
2478      %end;
2479  run;
2480  %mend dothis;
2481  %dothis
MLOGIC(DOTHIS):  Beginning execution.
MPRINT(DOTHIS):   data rule;
MPRINT(DOTHIS):   set test;
MLOGIC(DOTHIS):  %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
MPRINT(DOTHIS):   if find(results,trim("DEVICE1a")) then DEVICE1=1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable COL1 resolves to DEVICE1
MPRINT(DOTHIS):   else DEVICE1=0;
MLOGIC(DOTHIS):  %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
MPRINT(DOTHIS):   if find(results,trim("DEVICE2a")) then DEVICE2=1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable COL2 resolves to DEVICE2
MPRINT(DOTHIS):   else DEVICE2=0;
MLOGIC(DOTHIS):  %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
MPRINT(DOTHIS):   if find(results,trim("DEVICE3a")) then DEVICE3=1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable COL3 resolves to DEVICE3
MPRINT(DOTHIS):   else DEVICE3=0;
MLOGIC(DOTHIS):  %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
MPRINT(DOTHIS):   if find(results,trim("DEVICESa")) then DEVICES=1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable COL4 resolves to DEVICES
MPRINT(DOTHIS):   else DEVICES=0;
MLOGIC(DOTHIS):  %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
MPRINT(DOTHIS):   if find(results,trim("BANGLADESHa")) then BANGLADESH=1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable COL5 resolves to BANGLADESH
MPRINT(DOTHIS):   else BANGLADESH=0;
MLOGIC(DOTHIS):  %DO loop index variable I is now 6; loop will not iterate again.
MPRINT(DOTHIS):   run;

NOTE: There were 244524 observations read from the data set WORK.TEST.
NOTE: The data set WORK.RULE has 244524 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           34.26 seconds
      cpu time            5.30 seconds


MLOGIC(DOTHIS):  Ending execution.

That's what I got. I just changed the loop to finish at 5 as to 44 would be very very long.

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 17:01:24 GMT

In the log, we see

if find(rule_results,trim("DEVICE1a")) then DEVICE1=1;

If this is not what you want when the macro variable resolve, then please tell us what you do want.

Re: Looping through variables

jepafob — Thu, 21 May 2020 18:01:48 GMT

The variable &col1a is storing in that example string 'device1=Y'

So it should resolve to this one: 'device1=Y'.

In the log it looks like it resolving variable &col1 which is device1 in this example and appending an 'a' at the end which is not what it should be.

So to clear it out in this example:

&col1. = 'Device1'

&col1a='Device1=Y'

So it should resolve to:

if find(rule_results,trim("DEVICE1=Y")) then DEVICE1=1;

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 18:06:40 GMT

Given that you have defined the macro variables outside my ability to view them, please run this line of code and report what is written to the log.

%put &=col1a;

Re: Looping through variables

jepafob — Thu, 21 May 2020 18:23:02 GMT

it is exactly what I wrote in the previous post, do you see some discrepancy somewhere?

 
2482  %put &=col1a;
SYMBOLGEN:  Macro variable COL1A resolves to DEVICE1=Y
COL1A=DEVICE1=Y

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 18:28:45 GMT

These things do get complicated

Instead of

trim("&&col&i..a")

try removing one of the two dots

trim("&&col&i.a")

Re: Looping through variables

jepafob — Thu, 21 May 2020 19:31:04 GMT

It looks like it is working now. It is resolving correctly. Tomorrow I will test the results and report back.

I will be working tomorrow also on the similar pieces in this code, that could use a loop, following the similar logic and tricks you showed me here, but if I will be desperate I will post the next pieces I cannot figure out.

Thank you very much for your help 🙂
I am very new to all of this so did not know this double & or even this 'options mprint symbolgen mlogic;' which is super helpful. Thank you!

Re: Looping through variables

PaigeMiller — Thu, 21 May 2020 20:00:07 GMT

Another helpful thing is not to have macro variables that has some sort of index number that is not at the end but in the middle of the macro variable name. I am referring to &COL1A. The coding gets kind of confusing with &COL1A, as you have seen. If you made the macro variable name &COLA1 (yes, I know it looks like the name of a famous soft drink), the coding is much easier for &COLA1 &COLA2 etc. than it is for &COL1A &COL2A etc.

Re: Looping through variables

jepafob — Thu, 21 May 2020 21:28:21 GMT

Yeah, noticed that after your first solution. I am still learning how to name variables in practical way, very often I am lost. Thank you.

Re: Looping through variables

jepafob — Fri, 22 May 2020 12:27:31 GMT

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
...
);
quit;

It looks like everything else is working from the beginning of the topic. I validated the results today so thank you very much for your help.

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?

I have cut the conditions to &col5. but normally they might be up to even 50.

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.

Re: Looping through variables

PaigeMiller — Fri, 22 May 2020 12:32:39 GMT

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.

Re: Looping through variables

jepafob — Fri, 22 May 2020 12:53:05 GMT

Sorry.

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

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.

Re: Looping through variables

PaigeMiller — Fri, 22 May 2020 13:24:54 GMT

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.
How would the programmer know how many &COL variables there are?

Re: Looping through variables

jepafob — Fri, 22 May 2020 13:43:10 GMT

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.

Re: Looping through variables

PaigeMiller — Fri, 22 May 2020 14:01:12 GMT

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.