Using the Index Function with macro variables in a macro loop

[alan0101]

Hi

To keep it simple, I have two macro variables &T1 which equates to 'PTY', and &T2 which equates to 'LTD'.  When I run the below code, I get T1 as the output, not 'PTY'.  Please help

%macro Company;

%do i = 1 %to &n;

%put t&i;

%end;

%mend;

%Company;

[DMoovendhan]

Here you are resolving only &i  ie you are resolving the value to be t1 and t2.

if you need to resolve t1 and t2, you need to add && before t. the code will be

%macro Company;

%do i = 1 %to &n;

%put &&t&i;

%end;

%mend;

%Company;

[DMoovendhan]

beloow links will help you for better understanding,

http://www2.sas.com/proceedings/sugi22/CODERS/PAPER77.PDF

http://www.ats.ucla.edu/stat/sas/library/nesug98/p086.pdf

http://www.wuss.org/proceedings09/09WUSSProceedings/papers/cod/COD-Rosson.pdf

[alan0101]

Thanks !! Much appreciated

[alan0101]

Ok, so I still can'y get it to work in it's full context.  Below is the code and the log - can you see what the issue is ? :

351  options mlogic symbolgen;

352

353  %macro Company;

354  %do i = 1 %to &n;

355    data lib.set02;

356    Length Flag $20.;

357    Length Var1 $20.;

358    set lib.set01;

359    %if %index(%upcase(Surname),trim(&&t&i)) > 0 %then

360      %do;

361        Flag='Company';

362        Var1=t&i;

363        Output lib.set02;

364      %end;

365    run;

366  %end;

367  %mend;

368

369  %Company;

MLOGIC(COMPANY):  Beginning execution.

SYMBOLGEN:  Macro variable N resolves to 2

MLOGIC(COMPANY):  %DO loop beginning; index variable I; start value is 1; stop value is 2; by

      value is 1.

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 1

SYMBOLGEN:  Macro variable T1 resolves to PTY

MLOGIC(COMPANY):  %IF condition %index(%upcase(Surname),trim(&&t&i)) > 0 is FALSE

NOTE: Variable Flag is uninitialized.

NOTE: Variable Var1 is uninitialized.

NOTE: There were 11554 observations read from the data set LIB.SET01.

NOTE: The data set LIB.SET02 has 11554 observations and 52 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

MLOGIC(COMPANY):  %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 T2 resolves to LTD

MLOGIC(COMPANY):  %IF condition %index(%upcase(Surname),trim(&&t&i)) > 0 is FALSE

NOTE: Variable Flag is uninitialized.

NOTE: Variable Var1 is uninitialized.

NOTE: There were 11554 observations read from the data set LIB.SET01.

NOTE: The data set LIB.SET02 has 11554 observations and 52 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

MLOGIC(COMPANY):  %DO loop index variable I is now 3; loop will not iterate again.

MLOGIC(COMPANY):  Ending execution.

370

371  %macro Company;

372

373  %do i = 1 %to &n;

374  %put &&t&i;

375  %end;

376  %mend;

377

378  %Company;

MLOGIC(COMPANY):  Beginning execution.

SYMBOLGEN:  Macro variable N resolves to 2

MLOGIC(COMPANY):  %DO loop beginning; index variable I; start value is 1; stop value is 2; by

      value is 1.

MLOGIC(COMPANY):  %PUT &&t&i

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 1

SYMBOLGEN:  Macro variable T1 resolves to PTY

PTY

MLOGIC(COMPANY):  %DO loop index variable I is now 2; loop will iterate again.

MLOGIC(COMPANY):  %PUT &&t&i

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 2

SYMBOLGEN:  Macro variable T2 resolves to LTD

LTD

MLOGIC(COMPANY):  %DO loop index variable I is now 3; loop will not iterate again.

MLOGIC(COMPANY):  Ending execution.

[DMoovendhan]

Here the loop is outside the data step and each of the time youn execute this above program you  are looping the whole data step, I beleive you need to check the surname in each of the observation  for this you wouldn't have to loop the whole datastep.

If the loop is within the datastep that would help.

%macro Company;

data lib.set02;
Length Flag $20.;
Length Var1 $20.;
set lib.set01;
%do i = 1 %to &n;
%if %index(%upcase(Surname),trim(&&t&i)) > 0 %then
%do;
Flag='Company';
Var1=t&i;
Output lib.set02;
%end;
%end;
run;

%mend;

%Company;

[DMoovendhan]

Also the variables Flag and Var1 will get initialised only when the condition satisfies.

For this check instead of macro statements you could have used the normal IF Then statements.

[alan0101]

Thanks, at least the loop is working correctly now.  However, I know that the condition is satisfied when &t1 = 'PTY' and &t2='LTD', but for some reason the IF condition is still evaluating to FALSE, so Flag and var1 are not being populated.  I'll try some more text functions on the macro variable to see of that sorts it out e.g. LEFT, TRIM etc, unless you can think of anything else ?  By the way, the reason I'm using a macro is because the values for &t1 to &tn are populated from a text file with hundreds of different values, so the traditional if then statements would become way too cumbersome to manage.  Thanks for your help so far.

[RW9]

Hi,

To be frank, I don't understand the need for any of the macro code in your example.  Its not doing anything except obfuscating what you are trying to do.  Post some test data and required output, as I am sure your code could be simplified right down to one array and one loop.

[DMoovendhan]

Just one Suggestion, you can check if the data is upper case or lower case...

If its because of the case of the string, the you can use upcase....

[alan0101]

Thanks, I used the upcase function, but to no avail.  I will attach the relevant datasets and files

Here is the code that works before I put it in a macro :

DATA lib.CompInd;

  INFILE "&ProjPath\CompInd.txt" DLM='09'X DSD TRUNCOVER lrecl=1000 recfm=v;

  INPUT

  CompInd :$20.;

run;

proc sql noprint;

  select count(*) into :n

  from lib.CompInd;

quit;

%let n = &n;

%put &n;

proc sql noprint;

  select CompInd into :t1-:t&n from lib.CompInd;

quit;

%put &t1;

%put &t2;

%put &t3;

  data lib.set02;

  Length Flag $20.;

  Length Var1 $20.;

  set lib.set01;

  do i = 1 to &n;

  if index(upcase(Surname),'PTY') > 0 then

    do;

      Flag='Company';

      Var1='PTY';

      Output lib.set02;

    end;

  end;

  run;

This is the macro version of the above :

DATA lib.CompInd;

  INFILE "&ProjPath\CompInd.txt" DLM='09'X DSD TRUNCOVER lrecl=1000 recfm=v;

  INPUT

  CompInd :$20.;

run;

proc sql noprint;

  select count(*) into :n

  from lib.CompInd;

quit;

%let n = &n;

%put &n;

proc sql noprint;

  select CompInd into :t1-:t&n from lib.CompInd;

quit;

%put &t1;

%put &t2;

%put &t3;

options mlogic symbolgen;

%macro Company;

  data lib.set02;

  Length Flag $20.;

  Length Var1 $20.;

  set lib.set01;

  %do i = 1 %to &n;

  %let test=&&t&i;

  %put &test;

  %if %index(upcase(Surname),trim(&test)) > 0 %then

    %do;

      Flag='Company';

      Var1=&&t&i;

      Output lib.set02;

    %end;

  %end;

  run;

%mend;

%Company;

Now I just have to figure out how to attach the datasets ....

[RW9]

Hi,

Well here are four options which avoid all macro code (note option 4 has number hardcoded, but you could replace with a proc sql select count, as you did above.

---  This generatea a new datastep with the options

data _null_;
  set temp.compind end=last;
  if _n_=1 then call execute('data temp.want;
                                set temp.set01;
                                length flag var1 $20;');
  call execute(' if index(surname,"'||strip(compind)||'") > 0 then do;
                    flag="Company";
                    var1="'||strip(compind)||'";
                 end;');
  if last then call execute(';run;');
run;

---  This uses SQL update to actually change the existing dataset based on condition

proc sql;
  create table TEMP.WANT as
  select  SURNAME,
          "" as FLAG length=20,
          "" as VAR1 length=20
  from    TEMP.SET01;
  update TEMP.WANT A
  set FLAG="Company",
      VAR1=(select distinct THIS.COMPIND from TEMP.COMPIND THIS where index(A.SURNAME,THIS.COMPIND) > 0);
quit;

---  This use joining tables based on the condition

proc sql;
  create table TEMP.WANT as
  select  A.SURNAME,
          B.FLAG,
          B.COMPIND
  from    TEMP.SET01 A
  left join (select *,"Company" as FLAG from TEMP.COMPIND) B
  on      index(A.SURNAME,B.COMPIND) > 0;
quit;

--- This transposes you options so they can be iterated using an array and loop

proc transpose data=temp.compind out=compind2;
  var compind;
run;
proc sql;
  create table TEMP.WANT as
  select  A.*,
          B.*
  from    TEMP.SET01 A
  left join TEMP.COMPIND2 B
  on 1=1;
quit;
data temp.want (keep=surname flag var1);
  set temp.want;
  length flag var1 $20;
  array col{3};
  do i=1 to 3;
    if index(surname,col{i}) > 0 then do;
      flag="Company";
      var1=col{i};
    end;
  end;
run;

There are other options, hash, for instance.

[alan0101]

Thanks RW9 - I'm sure I'll find something in there that works !  Off to a strategy workshop now - will try the proc sql options first.