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);
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);
@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');
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.
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.
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.
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');
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.
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
Thank you for all the help. It worked out.
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?
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?
Thank you. I change the suffix to TERMD and the entire macro works fine. I really appreciate the help!
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);
@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');
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: