Looping through variables

[jepafob]

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...

[PaigeMiller]

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.

[jepafob]

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.

[jepafob]

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.

[PaigeMiller]

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.

[jepafob]

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.

[PaigeMiller]

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.

[jepafob]

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;

[PaigeMiller]

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;

[jepafob]

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

[PaigeMiller · Accepted solution]

These things do get complicated

Instead of 

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

try removing one of the two dots

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

[jepafob]

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!

[PaigeMiller]

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.

[jepafob]

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.

[jepafob]

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.