topic Re: Using the Index Function with macro variables in a macro loop in SAS Procedures

Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 07:52:38 GMT

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;

Re: Using the Index Function with macro variables in a macro loop

DMoovendhan — Thu, 20 Nov 2014 07:58:43 GMT

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;

Re: Using the Index Function with macro variables in a macro loop

DMoovendhan — Thu, 20 Nov 2014 08:01:00 GMT

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

Re: Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 08:03:10 GMT

Thanks !! Much appreciated

Re: Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 08:11:17 GMT

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.

Re: Using the Index Function with macro variables in a macro loop

DMoovendhan — Thu, 20 Nov 2014 08:21:13 GMT

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;

Re: Using the Index Function with macro variables in a macro loop

DMoovendhan — Thu, 20 Nov 2014 08:22:48 GMT

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.

Re: Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 08:42:13 GMT

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.

Re: Using the Index Function with macro variables in a macro loop

RW9 — Thu, 20 Nov 2014 09:29:43 GMT

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.

Re: Using the Index Function with macro variables in a macro loop

DMoovendhan — Thu, 20 Nov 2014 09:52:41 GMT

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

Re: Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 10:27:34 GMT

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;

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;

run;

%mend;

%Company;

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

Re: Using the Index Function with macro variables in a macro loop

RW9 — Thu, 20 Nov 2014 11:22:38 GMT

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.

Re: Using the Index Function with macro variables in a macro loop

alan0101 — Thu, 20 Nov 2014 11:28:18 GMT

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.