Troubleshooting output from a SAS Macro

[xliu1]

I need help with this SAS macro that is supposed to give me three outputs. However, after running this program, the first and second output have 0 records. But I know it should have records because when I run them separately, I got some numbers in each output.  Here is the SAS log: Any thoughts on how to modify this macro? Thanks.

%LET TERMD = 201608;	/* INSERT ONLY A TERMID WITH '05' = SUMMER OR '08' = FALL */
%LET TERMIDD = %bquote(&TERMD.);
%let ENR_Year = %substr(&termD.,1,4);
%let SUM_termid = %bquote(&ENR_Year.05);
%LET FALL_TERMID = %BQUOTE(&ENR_Year.08);
%LET SPR_TERMID = %BQUOTE(%EVAL(&ENR_Year. + 1)01);
%LET GRAD_2year = %bquote(%eval(&ENR_Year. + 2)05);%let GRAD_3Year=%bquote(%eval(&ENR_Year. + 3)05);
%let GRAD_4Year=%bquote(%eval(&ENR_Year. + 4)05);

%LET CAMPUS = '0001';

%PUT &GRAD_2year;
%PUT &GRAD_3year;
%PUT &GRAD_4year;

%Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
/*%Macro DEG ( YEAR3=);
%Macro DEG ( YEAR4=);*/
	/* NASPAA 2,3,4 YEAR GRADUATES STUDENTS  */
	PROC SQL;
		CREATE TABLE Grad2_&TERMD. as
			SELECT campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR2" and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad3_&TERMD. as
			SELECT campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR3" and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad4_&TERMD. as
			SELECT campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR4" and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT;
	QUIT;

%mend DEG;

/* 2 , 3 AND 4 YEAR COMP academic year 2014-2015 */
%DEG( YEAR2=&GRAD_2year);
%DEG( YEAR3=&GRAD_3year);
%DEG( YEAR4=&GRAD_4year);

[PaigeMiller]

When a macro is not working properly, turn on the macro debugging options with this command

options mprint symbolgen;

and then run the macro again. Show us the ENTIRE log for one run of the macro. Copy and paste the ENTIRE log for this one run of the macro as text (not screen capture) into the window that appears when you click on the </> icon. Please follow these instructions carefully.

[ballardw]

Suggest that you use a Missing(variablename) instead of <>'' or whatever to check for a missing value, or "not missing(variablename)" as appropriate..

Also, run the macro calls with Options Mprint; set and see if the code generated is actually what you expect to see.

Without any actual data we cannot tell why things don't match your expected counts. However since you calling the macro with only 1 of the 3 parameters defined you execute 2 Proc SQL without actual values for the parameters. So likely you are making a lot of garbage code.

For instance:

where TERM_DEG_GRANT <= "&YEAR2" and TERM_DEG_GRANT<>''

when you call the macro with the following then Year2 is not defined. So the above becomes "where term_deg_gran <= "" and Term_deg_grant<>'' which is never true. Cannot be "less than or equal to missing" and not equal to missing at the same time.  

%DEG( YEAR3=&GRAD_3year);
%DEG( YEAR4=&GRAD_4year);

Personally not sure why you think you want/need 3 sql steps using differently named macro variables to begin with.

[Reeza]

All of your outputs are created with this extension on the table name: &TERMD.

However that does not change between the macro calls so the output data sets are overwriting the previous results. I don't think this macro does what you think it does. Can you show working code for one iteration that works, a non macro version and explain why you want to create a macro?

&TERMD.

[xliu1]

Thank you for all the responses. This macro was written by previous co-workers and I am trying to learn how the macro works. I know how to get the results without using this macro. But I want to figure out why it is not working. I have modified the macro using different extention, so the output datasets will not be overwriting the previous results. I still do not get the correct results.  I know it is related to the way YEAR2, YEAR3, YEAR4 are defined, but I don't know how to revise that. Here is the sas log adn a screenshot of output file 

381 options mprint symbolgen;
382 %Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
383 /*%Macro DEG ( YEAR3=);
384 %Macro DEG ( YEAR4=);*/
385 /* NASPAA 2,3,4 YEAR GRADUATES STUDENTS */
386 PROC SQL;
387 CREATE TABLE Grad_2YEAR as
388 SELECT /*campus_code,HOMECAMPUS,
389 DEG_LEVEL_GRANT,*/
390 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2"
391 FROM OM_COHORT_DEGAWARD t1
392 where TERM_DEG_GRANT <= "&YEAR2" /*and TERM_DEG_GRANT<>''
393 group by campus_code,HOMECAMPUS,
394 DEG_LEVEL_GRANT*/;
395 QUIT;
396
397 PROC SQL;
398 CREATE TABLE Grad_3YEAR as
399 SELECT /*campus_code,HOMECAMPUS,
400 DEG_LEVEL_GRANT,*/
401 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3"
402 FROM OM_COHORT_DEGAWARD t1
403 where TERM_DEG_GRANT <= "&YEAR3" /*and TERM_DEG_GRANT<>''
404 group by campus_code,HOMECAMPUS,
405 DEG_LEVEL_GRANT*/;
406 QUIT;
407
408 PROC SQL;
409 CREATE TABLE Grad_4YEAR as
410 SELECT /*campus_code,HOMECAMPUS,
411 DEG_LEVEL_GRANT,*/
412 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4"
413 FROM OM_COHORT_DEGAWARD t1
414 where TERM_DEG_GRANT <= "&YEAR4" /*and TERM_DEG_GRANT<>''
415 group by campus_code,HOMECAMPUS,
416 DEG_LEVEL_GRANT*/;
417 QUIT;
418
419 %mend DEG;
420
421 /* 2 , 3 AND 4 YEAR COMP academic year 2014-2015
422 %DEG( YEAR2=&GRAD_2year);
423 %DEG( YEAR3=&GRAD_3year);
424 %DEG( YEAR4=&GRAD_4year);*/
425 %DEG( YEAR2='201805');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to '201805'
SYMBOLGEN: Macro variable YEAR2 resolves to '201805'
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201805'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201805'" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to
SYMBOLGEN: Macro variable YEAR3 resolves to
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to
SYMBOLGEN: Macro variable YEAR4 resolves to
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

426 %DEG( YEAR3='201905');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to
SYMBOLGEN: Macro variable YEAR2 resolves to
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to '201905'
SYMBOLGEN: Macro variable YEAR3 resolves to '201905'
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201905'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201905'" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to
SYMBOLGEN: Macro variable YEAR4 resolves to
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

427 %DEG( YEAR4='202005');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to
SYMBOLGEN: Macro variable YEAR2 resolves to
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to
SYMBOLGEN: Macro variable YEAR3 resolves to
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to '202005'
SYMBOLGEN: Macro variable YEAR4 resolves to '202005'
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'202005'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'202005'" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

%LET TERMD = 201608;	/* INSERT ONLY A TERMID WITH '05' = SUMMER OR '08' = FALL */
%LET TERMIDD = %bquote(&TERMD.);
%let ENR_Year = %substr(&termD.,1,4);
%let SUM_termid = %bquote(&ENR_Year.05);
%LET FALL_TERMID = %BQUOTE(&ENR_Year.08);
%LET SPR_TERMID = %BQUOTE(%EVAL(&ENR_Year. + 1)01);
%LET GRAD_2year = %bquote(%eval(&ENR_Year. + 2)05);
%let GRAD_3Year=%bquote(%eval(&ENR_Year. + 3)05);
%let GRAD_4Year=%bquote(%eval(&ENR_Year. + 4)05);
%LET CAMPUS = '0001';

%PUT &GRAD_2year;
%PUT &GRAD_3year;
%PUT &GRAD_4year;

options mprint symbolgen;
%Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
/*%Macro DEG ( YEAR3=);
%Macro DEG ( YEAR4=);*/
	/* NASPAA 2,3,4 YEAR GRADUATES STUDENTS  */
	PROC SQL;
		CREATE TABLE Grad_2YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR2" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad_3YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR3" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad_4YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR4" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

%mend DEG;

/* 2 , 3 AND 4 YEAR COMP academic year 2014-2015 
%DEG( YEAR2=&GRAD_2year);
%DEG( YEAR3=&GRAD_3year);
%DEG( YEAR4=&GRAD_4year);*/
%DEG( YEAR2='201805');
%DEG( YEAR3='201905');
%DEG( YEAR4='202005');

[PaigeMiller]

Repeating my instructions: 

Copy and paste the ENTIRE log for this one run of the macro as text (not screen capture) into the window that appears when you click on the </> icon. Please follow these instructions carefully.

[xliu1]

381  options mprint symbolgen;
382  %Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
383  /*%Macro DEG ( YEAR3=);
384  %Macro DEG ( YEAR4=);*/
385      /* NASPAA 2,3,4 YEAR GRADUATES STUDENTS  */
386      PROC SQL;
387          CREATE TABLE Grad_2YEAR as
388              SELECT /*campus_code,HOMECAMPUS,
389                  DEG_LEVEL_GRANT,*/
390                  COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2"
391              FROM OM_COHORT_DEGAWARD t1
392                  where TERM_DEG_GRANT <= "&YEAR2" /*and TERM_DEG_GRANT<>''
393                      group by campus_code,HOMECAMPUS,
394                          DEG_LEVEL_GRANT*/;
395      QUIT;
396
397      PROC SQL;
398          CREATE TABLE Grad_3YEAR as
399              SELECT /*campus_code,HOMECAMPUS,
400                  DEG_LEVEL_GRANT,*/
401                  COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3"
402              FROM OM_COHORT_DEGAWARD t1
403                  where TERM_DEG_GRANT <= "&YEAR3" /*and TERM_DEG_GRANT<>''
404                      group by campus_code,HOMECAMPUS,
405                          DEG_LEVEL_GRANT*/;
406      QUIT;
407
408      PROC SQL;
409          CREATE TABLE Grad_4YEAR as
410              SELECT /*campus_code,HOMECAMPUS,
411                  DEG_LEVEL_GRANT,*/
412                  COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4"
413              FROM OM_COHORT_DEGAWARD t1
414                  where TERM_DEG_GRANT <= "&YEAR4" /*and TERM_DEG_GRANT<>''
415                      group by campus_code,HOMECAMPUS,
416                          DEG_LEVEL_GRANT*/;
417      QUIT;
418
419  %mend DEG;
420
421  /* 2 , 3 AND 4 YEAR COMP academic year 2014-2015
422  %DEG( YEAR2=&GRAD_2year);
423  %DEG( YEAR3=&GRAD_3year);
424  %DEG( YEAR4=&GRAD_4year);*/
425  %DEG( YEAR2='201805');
MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR2 resolves to '201805'
SYMBOLGEN:  Macro variable YEAR2 resolves to '201805'
MPRINT(DEG):   CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201805'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201805'" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR3 resolves to
SYMBOLGEN:  Macro variable YEAR3 resolves to
MPRINT(DEG):   CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR4 resolves to
SYMBOLGEN:  Macro variable YEAR4 resolves to
MPRINT(DEG):   CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


426  %DEG( YEAR3='201905');
MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR2 resolves to
SYMBOLGEN:  Macro variable YEAR2 resolves to
MPRINT(DEG):   CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR3 resolves to '201905'
SYMBOLGEN:  Macro variable YEAR3 resolves to '201905'
MPRINT(DEG):   CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201905'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201905'" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR4 resolves to
SYMBOLGEN:  Macro variable YEAR4 resolves to
MPRINT(DEG):   CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


427  %DEG( YEAR4='202005');
MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR2 resolves to
SYMBOLGEN:  Macro variable YEAR2 resolves to
MPRINT(DEG):   CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR3 resolves to
SYMBOLGEN:  Macro variable YEAR3 resolves to
MPRINT(DEG):   CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(DEG):   PROC SQL;
SYMBOLGEN:  Macro variable YEAR4 resolves to '202005'
SYMBOLGEN:  Macro variable YEAR4 resolves to '202005'
MPRINT(DEG):   CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'202005'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'202005'" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

[xliu1]

Thank you for all the help. It worked out.

[Reeza]

You probably want to change the suffix of the table back to TERMD as well.

[xliu1]

can you please elaborate on why I change the suffix of the table back to TERMD? TERMD has been defined to be equal to 201608, which is the starting year for this group. If I add this suffix to each table, the output will overwrite previous output?

[Reeza]

No it won't overwrite anything because you're not calling it multiple times now, just once. This ensures any further processes that use this data will work as expected and you don't have to change them. 

---

@xliu1 wrote:

can you please elaborate on why I change the suffix of the table back to TERMD? TERMD has been defined to be equal to 201608, which is the starting year for this group. If I add this suffix to each table, the output will overwrite previous output?

---

[xliu1]

Thank you. I change the suffix to TERMD and the entire macro works fine. I really appreciate the help!

[Reeza · Accepted solution]

I think you're calling the macro incorrectly, it looks like it was intended to run for all three years at once and you're calling it multiple times once for each year instead. 

%DEG( YEAR2= &grad_2year, YEAR3=&grad_3year, YEAR4=&grad_4year);

Spoiler

---

@xliu1 wrote:

Thank you for all the responses. This macro was written by previous co-workers and I am trying to learn how the macro works. I know how to get the results without using this macro. But I want to figure out why it is not working. I have modified the macro using different extention, so the output datasets will not be overwriting the previous results. I still do not get the correct results.  I know it is related to the way YEAR2, YEAR3, YEAR4 are defined, but I don't know how to revise that. Here is the sas log adn a screenshot of output file 

381 options mprint symbolgen;
382 %Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
383 /*%Macro DEG ( YEAR3=);
384 %Macro DEG ( YEAR4=);*/
385 /* NASPAA 2,3,4 YEAR GRADUATES STUDENTS */
386 PROC SQL;
387 CREATE TABLE Grad_2YEAR as
388 SELECT /*campus_code,HOMECAMPUS,
389 DEG_LEVEL_GRANT,*/
390 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2"
391 FROM OM_COHORT_DEGAWARD t1
392 where TERM_DEG_GRANT <= "&YEAR2" /*and TERM_DEG_GRANT<>''
393 group by campus_code,HOMECAMPUS,
394 DEG_LEVEL_GRANT*/;
395 QUIT;
396
397 PROC SQL;
398 CREATE TABLE Grad_3YEAR as
399 SELECT /*campus_code,HOMECAMPUS,
400 DEG_LEVEL_GRANT,*/
401 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3"
402 FROM OM_COHORT_DEGAWARD t1
403 where TERM_DEG_GRANT <= "&YEAR3" /*and TERM_DEG_GRANT<>''
404 group by campus_code,HOMECAMPUS,
405 DEG_LEVEL_GRANT*/;
406 QUIT;
407
408 PROC SQL;
409 CREATE TABLE Grad_4YEAR as
410 SELECT /*campus_code,HOMECAMPUS,
411 DEG_LEVEL_GRANT,*/
412 COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4"
413 FROM OM_COHORT_DEGAWARD t1
414 where TERM_DEG_GRANT <= "&YEAR4" /*and TERM_DEG_GRANT<>''
415 group by campus_code,HOMECAMPUS,
416 DEG_LEVEL_GRANT*/;
417 QUIT;
418
419 %mend DEG;
420
421 /* 2 , 3 AND 4 YEAR COMP academic year 2014-2015
422 %DEG( YEAR2=&GRAD_2year);
423 %DEG( YEAR3=&GRAD_3year);
424 %DEG( YEAR4=&GRAD_4year);*/
425 %DEG( YEAR2='201805');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to '201805'
SYMBOLGEN: Macro variable YEAR2 resolves to '201805'
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201805'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201805'" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to
SYMBOLGEN: Macro variable YEAR3 resolves to
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to
SYMBOLGEN: Macro variable YEAR4 resolves to
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

426 %DEG( YEAR3='201905');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to
SYMBOLGEN: Macro variable YEAR2 resolves to
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to '201905'
SYMBOLGEN: Macro variable YEAR3 resolves to '201905'
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'201905'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'201905'" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to
SYMBOLGEN: Macro variable YEAR4 resolves to
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

427 %DEG( YEAR4='202005');
MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR2 resolves to
SYMBOLGEN: Macro variable YEAR2 resolves to
MPRINT(DEG): CREATE TABLE Grad_2YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_2YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR3 resolves to
SYMBOLGEN: Macro variable YEAR3 resolves to
MPRINT(DEG): CREATE TABLE Grad_3YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "" ;
NOTE: Table WORK.GRAD_3YEAR created, with 1 rows and 1 columns.

MPRINT(DEG): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MPRINT(DEG): PROC SQL;
SYMBOLGEN: Macro variable YEAR4 resolves to '202005'
SYMBOLGEN: Macro variable YEAR4 resolves to '202005'
MPRINT(DEG): CREATE TABLE Grad_4YEAR as SELECT COUNT(DISTINCT(t1.UID)) AS GRAD_"'202005'" FROM
OM_COHORT_DEGAWARD t1 where TERM_DEG_GRANT <= "'202005'" ;
NOTE: Table WORK.GRAD_4YEAR created, with 1 rows and 1 columns.

%LET TERMD = 201608;	/* INSERT ONLY A TERMID WITH '05' = SUMMER OR '08' = FALL */
%LET TERMIDD = %bquote(&TERMD.);
%let ENR_Year = %substr(&termD.,1,4);
%let SUM_termid = %bquote(&ENR_Year.05);
%LET FALL_TERMID = %BQUOTE(&ENR_Year.08);
%LET SPR_TERMID = %BQUOTE(%EVAL(&ENR_Year. + 1)01);
%LET GRAD_2year = %bquote(%eval(&ENR_Year. + 2)05);
%let GRAD_3Year=%bquote(%eval(&ENR_Year. + 3)05);
%let GRAD_4Year=%bquote(%eval(&ENR_Year. + 4)05);
%LET CAMPUS = '0001';

%PUT &GRAD_2year;
%PUT &GRAD_3year;
%PUT &GRAD_4year;

options mprint symbolgen;
%Macro DEG ( YEAR2=, YEAR3=, YEAR4=);
/*%Macro DEG ( YEAR3=);
%Macro DEG ( YEAR4=);*/
	/* NASPAA 2,3,4 YEAR GRADUATES STUDENTS  */
	PROC SQL;
		CREATE TABLE Grad_2YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR2" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR2" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad_3YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR3" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR3" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

	PROC SQL;
		CREATE TABLE Grad_4YEAR as
			SELECT /*campus_code,HOMECAMPUS,
				DEG_LEVEL_GRANT,*/
				COUNT(DISTINCT(t1.UID)) AS GRAD_"&YEAR4" 
			FROM OM_COHORT_DEGAWARD t1							
				where TERM_DEG_GRANT <= "&YEAR4" /*and TERM_DEG_GRANT<>''
					group by campus_code,HOMECAMPUS,
						DEG_LEVEL_GRANT*/;
	QUIT;

%mend DEG;

/* 2 , 3 AND 4 YEAR COMP academic year 2014-2015 
%DEG( YEAR2=&GRAD_2year);
%DEG( YEAR3=&GRAD_3year);
%DEG( YEAR4=&GRAD_4year);*/
%DEG( YEAR2='201805');
%DEG( YEAR3='201905');
%DEG( YEAR4='202005');

---