topic Re: Creating different numbers of macro variables in SAS Programming

Creating different numbers of macro variables

GregG — Tue, 28 May 2013 17:00:18 GMT

What I have:

%let year_cohort = 03; /* it needs to have a single leading 0 if less than 10 */

%let AY_start = &year_cohort +1;

%let AY_end = &year_cohort + 6;

%let AY_YEAR1=04;/* it needs to have a single leading 0 if less than 10 */

%let AY_YEAR2=05;

%let AY_YEAR3=06;

%let AY_YEAR4=07;

%let AY_YEAR5=08;

%let AY_YEAR6=09;

What I want (except, working):

%let year_cohort = 03;

%let num_of_years = 6;

%let AY_start = &year_cohort + 1;

%let AY_end = &year_cohort + &num_of_years;

and then I would like the AY_YEARi to be created based on the number contained in the &num_of_years variable (in this case, 6).

Re: Creating different numbers of macro variables

Reeza — Tue, 28 May 2013 17:19:31 GMT

I'd use a data _null_ step with a do loop to create them:

%let year_cohort=01;

%let num_years=10;

data _null_;

start=&year_cohort;

call symput('ay_start', put(start+1, z2.));

call symput('ay_end', put(start+&num_years, z2.));

year_count=1;

do i=1 to &num_years;

call symput("ay_year"||compress(put(i, 8.)), put(start+i, z2.));

end;

run;

*Check macro variables are created ok;

%put &ay_start;

%put &ay_end;

%put &ay_year1;

%put &ay_year3;

%put &ay_year6;

%put &ay_year9;

Re: Creating different numbers of macro variables

Astounding — Tue, 28 May 2013 17:31:53 GMT

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.

Re: Creating different numbers of macro variables

GregG — Tue, 28 May 2013 18:10:08 GMT

Thank you both! The first I tried worked as intended, though I may need to explore the second for another piece of this puzzle at a later date.

Thank you again!

Re: Creating different numbers of macro variables

GregG — Tue, 28 May 2013 18:48:41 GMT

What does the year_count = 1; in the middle of the data _null_ step do?

Re: Creating different numbers of macro variables

Reeza — Tue, 28 May 2013 18:52:03 GMT

nothing...you can delete it. I was originally going to use it as a counter but realized that I could use the i instead.

Re: Creating different numbers of macro variables

GregG — Tue, 28 May 2013 20:56:13 GMT

Additional question (should this be asked separately?)

Can I create one macro variable that is essentially the concatenation of those created in the do loop?

I want to replace this:

array Grad_year {1:6} $ ("&AY_YEAR1" "&AY_YEAR2" "&AY_YEAR3" "&AY_YEAR4" "&AY_YEAR5" "&AY_YEAR6");

With something like this:

array Grad_year {1:&num_years} $ (&ay_years);

Re: Creating different numbers of macro variables

Reeza — Tue, 28 May 2013 21:05:41 GMT

You should be able to use the start and end variables instead.

array Grad_year {1:&num_years} $ &ay_start-&ay_end;

Re: Creating different numbers of macro variables

GregG — Tue, 28 May 2013 21:41:41 GMT

Thank you, but it isn't working as expected.

Here is my input:

%let year_cohort=03;

%let num_years=6;

data _null_;

start=&year_cohort;

call symput('ay_start', put(start+1, z2.));

call symput('ay_end', put(start+&num_years, z2.));

do i=1 to &num_years;

call symput("ay_year"||compress(put(i, 8.)), put(start+i, z2.));

end;

run;

data test;

array Grad_year {1:&num_years} (&ay_start-&ay_end);

run;

and here is the log:

391 %let year_cohort=03;

392 %let num_years=6;

393

394 data _null_;

395 start=&year_cohort;

396 call symput('ay_start', put(start+1, z2.));

397 call symput('ay_end', put(start+&num_years, z2.));

398

399 do i=1 to &num_years;

400 call symput("ay_year"||compress(put(i, 8.)), put(start+i, z2.));

401 end;

402 run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

403

404 data test;

405 array Grad_year {1:&num_years} (&ay_start-&ay_end);

WARNING: Partial value initialization of the array Grad_year.

406 run;

NOTE: The data set WORK.TEST has 1 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

And the dataset is as follows:

Grad_year1	Grad_year2	Grad_year3	Grad_year4	Grad_year5	Grad_year6
4	-9	.	.	.	.

It's reconciling to 4 -9 instead of 04-09 (in this case).

I'll keep trying things along those lines. Thank you again!

ETA: I've also tried using spaces ( &ay_start - &ay_end ) and quotes ("&ay_start" - "&ay_end") with different, although still not working, results.

Re: Creating different numbers of macro variables

Reeza — Tue, 28 May 2013 21:54:39 GMT

Take a look at your original question, that was what you specified, ie ay_year1 ay_year2, not ay_year01 vs ay_year02

I think you're confusing macro variable names and values.

There is a mistake though, this should be as follows.

array Grad_year {&num_years} (&ay_start-&ay_end);

You may want to go back to the start and describe more what your'e doing though. Because you probably could just do the following instead:

array Grad_year {*} (&ay_start-&ay_end);

Re: Creating different numbers of macro variables

GregG — Tue, 28 May 2013 22:13:48 GMT

You're right (of course) - I want macro variable AY_Year1 to have a value of 04 (in this case).

So in the above table that I inserted, Grad_year1 is almost correct (I want it to say 04, not 4), but Grad_year2 is incorrect (-9, it should say 05).

I suspect what I'm doing is just approached the wrong way to begin with. I've been using SAS for less than 6 months, and I haven't had much time to actually learn what I am doing, just "Googling" this and that, etc.

This:

array Grad_year {&num_years} (&ay_start-&ay_end);

does the same thing as this:

array Grad_year {1:&num_years} (&ay_start-&ay_end);

which is to say, Grad_year1 = 4 (instead of 04) and Grad_year2 = -9 (instead of 05)

%put &ay_year1 = 04

Re: Creating different numbers of macro variables

Reeza — Tue, 28 May 2013 22:33:20 GMT

Hi Greg,

Can you go way back and explain what you're trying to do. I don't think you need macro's at all, but until I know more I can only guess.

Re: Creating different numbers of macro variables

Tom — Wed, 29 May 2013 03:39:44 GMT

What is it that you really want to do? Do you want to put values into datasets or create macro variables?

Usually the goal of SAS programming is to manipulate data and macro programming is resorted to when more flexibility is needed that what SAS programming can do.

For example you seem concerned that the numeric variable GRAD_YEAR1 had a 4 instead of 04, but since it is a numeric variable there is no difference. Perhaps you just want to add a format such as Z2. to the variable so that numbers less than 10 print with a leading zero? Perhaps you would rather have GRAD_YEAR1 be defined as a character variable instead? Then you could store '04' into it instead of '4' and it would actually be a different value.

Re: Creating different numbers of macro variables

GregG — Thu, 30 May 2013 22:21:53 GMT

Okay, I was given the following with the idea of only changing two variables - the starting year (year_cohort) and the number of years. The comments below are the comments as I was given them.

%let year_cohort=03;/*the fall semester belongs to academic year + 1*/

%let AY_start = &year_cohort + 1;/*The fall academic year*/

%let AY_end = &year_cohort + 6;

%let AY_YEAR1=04;

%let AY_YEAR2=05;

%let AY_YEAR3=06;

%let AY_YEAR4=07;

%let AY_YEAR5=08;

%let AY_YEAR6=09;

/*EACH GRAD DATA SET REPRESENT AN ACADEMIC YEAR OF GRADUATION DATA. GRAD_1, FOR EXAMPLE, REPRESENTS GRADUATION DATA */

/*STARTING FROM C0HORT FALL SEMESTER (DECEMBER GRADUATION) THROUGH THE SUMMER GRADUATION ENDING THE ACADEMIC YEAR.*/

DATA GRAD_1(KEEP = CRED_ID Id College_1 MAJOR_1 CIP_1 Degree1_C1 Degree_1 Lvl_1 SEMESTER_GRADUATED_1 GRADUATE_DATE_1 )

GRAD_2 (KEEP = CRED_ID ID College_2 MAJOR_2 CIP_2 Degree1_C2 Degree_2 Lvl_2 SEMESTER_GRADUATED_2 GRADUATE_DATE_2 )

GRAD_3 (KEEP= CRED_ID ID College_3 MAJOR_3 CIP_3 Degree1_C3 Degree_3 Lvl_3 SEMESTER_GRADUATED_3 GRADUATE_DATE_3 )

GRAD_4 (KEEP= CRED_ID ID College_4 MAJOR_4 CIP_4 Degree1_C4 Degree_4 Lvl_4 SEMESTER_GRADUATED_4 GRADUATE_DATE_4 )

GRAD_5 (KEEP= CRED_ID ID College_5 MAJOR_5 CIP_5 Degree1_C5 Degree_5 Lvl_5 SEMESTER_GRADUATED_5 GRADUATE_DATE_5 )

GRAD_6 (KEEP= CRED_ID ID College_6 MAJOR_6 CIP_6 Degree1_C6 Degree_6 Lvl_6 SEMESTER_GRADUATED_6 GRADUATE_DATE_6) ;

merge GRADUATE (in=a) cip;

by major LVL_DEGREE;

if a;

LENGTH SEMESTER_GRADUATED $3.;

GRADUATE_DATE=datepart(GRAD_DATE);/*ORACLE DATES HAVE TIME COMPONENTS TO THEM. THIS SAS FUNCTION JUST TAKES THE DATE PART.*/

/*CREATE SEMESTER GRADUATED VARIABLE:First two characters contain the ACADEMIC year. THREE GRADUATION MONTHS IN AN ACADEMIC YEAR: 12, 05, 08*/

IF PUT(MONTH(GRADUATE_DATE),z2.)='12' THEN SEMESTER_GRADUATED=SUBSTR(ACADEMIC_YEAR,1,2)||'1';

ELSE IF PUT(MONTH(GRADUATE_DATE),z2.)='05' THEN SEMESTER_GRADUATED=SUBSTR(ACADEMIC_YEAR,1,2)||'2';

ELSE IF PUT(MONTH(GRADUATE_DATE),z2.)='08' THEN SEMESTER_GRADUATED=SUBSTR(ACADEMIC_YEAR,1,2)||'4';

*keep Id College MAJOR CIP Degree1_w Degree2 Lvl SEMESTER_GRADUATED GRADUATE_DATE;

/*An array of character values and not variable names. Hence, the $ and parenthesis around the list*/

array Grad_year {1:6} $ ("&AY_YEAR1" "&AY_YEAR2" "&AY_YEAR3" "&AY_YEAR4" "&AY_YEAR5" "&AY_YEAR6");

/*CREATE VARIABLES FOR EACH GRADUATION ACADEMIC YEAR*/

%MACRO CREATE_VAR(START,END);

%DO I=&START %TO &END;

IF ACADEMIC_YEAR=grad_year{&I} THEN do;

/*COMES FROM CIP FILE*/

College_&I= College_C; CIP_&I=CIP; Degree1_C&I=DEGREE1_C;Degree_&I=DEGREE; Lvl_&I=LVL_C;

/*COMES FROM GRADUATE FILE*/

MAJOR_&I=major; SEMESTER_GRADUATED_&I=SEMESTER_GRADUATED; GRADUATE_DATE_&I = GRADUATE_DATE;

FORMAT GRADUATE_DATE_&I MMDDYY8.;

OUTPUT GRAD_&I;

end;

%END;

%MEND CREATE_VAR;

%CREATE_VAR(1,6);

RUN;

-----------------

So you can see the questions I have above, and where I am trying to go with them. Sorry about not providing this at the beginning. I wasn't sure sharing this much would be helpful.

Again, the idea is to only have to change year_cohort= and to add a num_years variable to set the number of years, and have everything else fall into place from there.

Re: Creating different numbers of macro variables

Tom — Fri, 31 May 2013 00:21:36 GMT

I assume that the reason you were originally concentrating on the the leading zeros for the years is because the variable ACADEMIC_YEAR from you input dataset is character instead of numeric. Why not just convert it to a number and stop worrying about the leading zeros?

So instead of IF ACADEMIC_YEAR=grad_year{&I} ... (which because the way you have created the GRAD_YEAR arrray is the same is IF ACADEMIC_YEAR= '03' ) you could use IF input(ACADEMIC_YEAR,2.) = 3 and so not have to worry about leading zeros.

Why do you need the cohort datasets? Why not just create a single dataset with a cohort variable?

Is YEAR_COHORT the only input or is the upper bound of 6 years also an input variable?

Do you have to deal with century boundaries? What if YEAR_COHORT was 98 (ie 1998)?

data want ;

merge GRADUATE (in=a) cip;

by major LVL_DEGREE;

if a;

cohort = input(academic_year,2.) - &Year_Cohort + 1;

if cohort < 1 or cohort > 6 then delete;

/* You could have the other reformatting things here */

run;

Then if you really want to split it into 6 separate datasets then you could generate code for that.

Re: Creating different numbers of macro variables

GregG — Fri, 31 May 2013 00:50:23 GMT

I think the idea is to track the person overtime (again, I was given this to "explore").

So a dataset like so:

001 09/FA other fields/variables

001 10/FA other fields/variables

001 11/FA other fields/variables

would become:

001 09/FA (09/FA related variables) 10/FA (related variables) 11/FA (related variables)

I don't foresee having to deal with century boundaries at this time, 10 years seems to be the most we would deal with at any one time.

It's not that I want to split into 6 years, I want to split into n years, where n is set by the user in a macrovariable (num_years) at the beginning of the program (along with the year_cohort).

Re: Creating different numbers of macro variables

GregG — Fri, 31 May 2013 00:51:21 GMT

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!

Re: Creating different numbers of macro variables

Tom — Fri, 31 May 2013 03:47:42 GMT

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;

Re: Creating different numbers of macro variables

GregG — Tue, 04 Jun 2013 14:00:31 GMT

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!