I greatly appreciate the help. I'm still new to SAS - but I've started the Programming Essentials I course, so hopefully that along with asking questions will help me learn.
Thank you again!
It sounds like you want to roll the values up into a single observation per student.
That is a common programming pattern that is normally handled by using arrays and does NOT require macro programming. You can just use the macro variables. Use &NUM_YEARS to set the number of variables needed to be created. Use &YEAR_COHORT to calculate the index into the array be subtracting from the ACADEMIC_YEAR variable in the data.
So something like below should do it. You will need to expand to all of the variables. For the character variables you will need to define the length in the ARRAY statement so that SAS knows how long to make the variables. If have added what is called a DOW loop. That is the MERGE statement is inside of a DO loop. This will let it read all of the observations for the same ID value in one pass and fill up the array values. So you will not need to split out into separate datasets and then merge back together to get them into one observation.
I have not listed the variable names in the array statement so SAS will automatically add the numeric digits to the end. This is a problem for DEGREE1_C array as you cannot use the same name for the array as an existing variable, so I have add and underscore so the arrayed variable names will be DEGREE1_C_1 to DEGREE1_C_&num_years. You could also just use a different value for the array name and list explicitly the variable names using a variable list as I have just done in the previous sentence.
%let year_cohort=03;
%let num_years=6;
data want;
array college_ (&num_years) $10; /* You need to specify the length for the character variables */
array cip_ (&num_years) $10 ;
array major_ (&num_years) $10;
array degree1_c_ (&num_years) $10 ;
....
do until (last.ID) ;
merge .... ;
by id ;
if A then do;
relative_year = input(academic_year,2.) - &year_cohort + 1;
if (1 <= relative_year <= &num_years) then do;
college_(relative_year) = college_c;
cip_(relative_year) = cip;
major_(relative_year) = major ;
degree1_c_(relative_year) = degree1_c ;
...
end; * END if year is within window ;
end; * END if data is from graduate dataset ;
end; * END of DO loop ;
run;
So I'm trying to implement this, and I'm running to an issue here:
by id ;
The Graduates table does have an 'id' field, but the CIP table does not.
Both have 'major' and 'lvl_degree', which is how they were being merged originally.
Nevermind - I figured out that I needed to do it a different way.
If first sort and merge the 2 datasets by major lvl_degree in a previous step.
Then, in the do until (last.id), I set (instead of merge) the previously merged dataset by id. This appears to do exactly what I want.
Thank you all so much again!
This code needs to go inside a macro definition, and you may need to pay attention to which variables are local and which are global. But that being said, here's one way to go about it:
%local i;
%do i=1 %to &num_of_years;
%let ay_year&i = %sysfunc(putn(&year_cohort+&i, z2));
%end;
Good luck.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.