BookmarkSubscribeRSS Feed
alan0101
Obsidian | Level 7

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;

12 REPLIES 12
DMoovendhan
Quartz | Level 8


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;

alan0101
Obsidian | Level 7

Thanks !! Much appreciated

alan0101
Obsidian | Level 7

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
Quartz | Level 8

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
Quartz | Level 8

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4028 views
  • 0 likes
  • 3 in conversation