Iterative Processing with Macros

Reply
Frequent Contributor
Posts: 121

Iterative Processing with Macros

I have managed to better formulate my question and code. Again, the question is this.

With regards to the attached 'example' file/dataset (after reading into SAS using code below), I am trying to make it so that I can create a new 'hpi1' dataset by dropping the first three columns of dataset 'Example' and adding three columns at the end of the newly created dataset 'hpi1', such that the three newly-added columns equal the last column of the new hpi1 dataset. I am then trying to repeat this process to generate a new dataset 'hpi2', by dropping the first three columns of the new dataset 'hpi1' and adding three new columns at the end of the newly created hpi2 dataset; the three new columns would equal the last column in the 'hpi1' dataset. Ideally I would like to iterate this process 4 times, always dropping three columns from and adding three columns to the number of columns in the most recently created dataset.

Below is the code that I have come up with so far. I need to get the outer loop to work; I think that the inner loop is fine. SAS doesn't like my way of incrementing the outter loop.

Could anyone please offer their thoughts? I feel that I am close to my solution, but I am currently stuck. Any help would be extremely appreciated.

Thanks in advance.

Code that I need help with:

options mprint mlogic symbolgen;

%let nofiles=2;

data hpi&nofiles;
    set example end=last;
       do i=1 to &nofiles;
         %let arrlowbound=1;
         %let arrelements=15;
         %let arrupbound=%eval(&arrlowbound+14);
         array month{&elements} month&arrlowbound-month&arrupbound;
          do i=&arrlowbound to &arrupbound;
            %let increment=3;
            drop month&arrlowbound-month&increment;
             if last then do;
               %let No1=%eval(&arrupbound+1);
               %let No2=%eval(&arrupbound+2);
               %let No3=%eval(&arrupbound+3);
               month&No1=month(&arrupbound);
               month&No2=month(&arrupbound);
               month&No3=month(&arrupbound);
             end;
            drop i;
           %eval(&arrlowbound+3);
          end;
          output hpi&nofiles;
run;

Code to read attached file into SAS:

data example;

  infile '/folders/example.txt' dlm='090D'x LRecL=10000;

  input location $2. month1-month15;

  format month1-month15 best15.12;

run;

Attachment
Super User
Posts: 10,500

Re: Iterative Processing with Macros

One major issue: you have two loops using i as the iterator. While that will not create a syntax error it will keep reseting i in ways you don't want and possibly create an infinite loop. Any nested do loops should have a different iterator. Not extremely well documented feature if you use _i_ construct for your loop variables they are temporary variables and not added to the dataset.

There is nothing gained by putting all of the macro %let assignments inside a datastep as they work at compile time.

Get a version that works without any macro assignments. Then work on adding macro language, which can likely be much simpler with an actual %macro construct as that is probably where you want to control loops.

Super User
Posts: 17,835

Re: Iterative Processing with Macros

What are you actually trying to do here? Or perhaps why? It doesn't make sense to me to generate these sets this way. You could do it all in one step and control the output, but I'm going to assume you simply aren't carrying forward the same 3 variable values but manipulating them somehow.

I'd suggest transposing, generating your data then re-transposing. Regardless, something to consider is that SAS works better when it process data down rather than across and storing your data in a different format may make your life infinitely easier.

data example;

  infile 'Z:\Dropbox\SAS\XML\example.txt' dlm='090D'x LRecL=10000;

  input location $2. month1-month15;

  format month1-month15 best15.12;

run;

proc sort data=example; by location;

run;

proc transpose data=example out=sample (rename=_name_=monthc)  prefix=value;

by location;

run;

data add3;

  set sample;

  by location;

  retain count;

  month=input(compress(monthc, , 'kd'), 3.);

  if first.location then count=1;

  else count+1;

  if count>3 then output;

  if last.location then do i=1 to 3;

  month=month+1;

  monthc=cats("month", put(month, 2. -l));

  output;

  end;

run;

proc transpose data=add3 out=hpi2;

by location;

id monthc;

var value1;

run;


Frequent Contributor
Posts: 121

Re: Iterative Processing with Macros

Thanks, this is very helpful; transposing never occured to me. To answer your question, indeed, I am not carrying forward the same 3 variables. Rather, I am taking in an initial dataset, containing a large number of similar variables (hence the array definition); I then drop the first three of the similar variables and add three similar variables (whose value is equal to that of the last value of the previous array). The goal is to iterate through the adding and dropping of variables 9 times and using the output dataset from each iteration as the input dataset to the next.

Ask a Question
Discussion stats
  • 3 replies
  • 332 views
  • 6 likes
  • 3 in conversation