SAS: How to loop a macro over rows of data to change to missing

Question

Can anyone help with this issue I'm having where the macro is only taking the final row value of the data?

I have some data that looks like this:

data data1 ;  
  infile datalines dsd dlm='|' truncover;  
  input id :$2. year_age_15 EDU_2000 EDU_2001 EDU_2002 ;  
datalines4;  
10|2000|3|4|5  
11|2000|5|5|6  
12|2001|1|2|3  
13|2002|5|5|6  
14|2001|2|2|2  
15|2000|3|3|4   
;;;;  

However I need it to use the year variable to determine which data to keep, and then change all the values for the years after that value to missing, like so:

data data1 ;  
  infile datalines dsd dlm='|' truncover;  
  input id :$2. year_age_15 EDU_2000 EDU_2001 EDU_2002 ;  
datalines4;  
10|2000|3|.|.   
11|2000|5|.|.   
12|2001|1|2|.  
13|2002|5|5|6  
14|2001|2|2|.  
15|2000|3|.|.  
;;;;  

I've been trying to get this macro to work, but it only works intermittently and works just for the final row of the data rather than looping through the rows.

%macro macro2 (output=, input=);  
data &output;  
set &input;  
%DO I = 1 %TO 6;  
%do; call symput('value2',trim(left(put(year_age_15,8.))));   
temp_col=&value2.;  
%let year_end=&value2.;  
%put YEAR END IS: &year_end.;  
%put EDU YEAR IS:  EDU_&year_end.;  
%do year = &year_end. %TO 2002;  
%put &year.;  
EDU_&year.=.;  
%end;  
%end;  
%end;  
run;  
%MEND macro2;  
%macro1(input=testset, output=output_testset);  

In R it could be something simple like :
for(i in 1:6){. do this }

Any advice? I can't figure out which bit is going wrong, thanks!

Answer 1

As @Joe mentions, the year to match is part of a variable name, which is tremor inducing 'data in the metadata'

You can use the VNAME to retrieve the variable name of an index accessed array element. Use that feature to compare to expected variable name whilst looping over a variable array based on variables named EDU*.

Example:

data have ;  
  infile datalines dsd dlm='|' truncover;  
  input id :$2. year_age_15 EDU_2000 EDU_2001 EDU_2002 ;  
datalines4;  
10|2000|3|4|5  
11|2000|5|5|6  
12|2001|1|2|3  
13|2002|5|5|6  
14|2001|2|2|2  
15|2000|3|3|4   
;;;;

data want;
  set have;
  array edus edu_:;

  * find index of element corresponding to variable name having year;
  do _n_ = 1 to dim(edus) until (upcase(vname(edus(_n_))) = cats('EDU_',year_age_15));
  end;

  * fill in elements at indices post the found one with missing values;
  do _n_ = _n_+1 to dim(edus);
    call missing(edus(_n_));
  end;  
run;