Hi All,
Following is my data and code to print a student transcript. Can someone tell me how do I add the following three things(see attached):
1. SYSDATE as print date on the top right (see picture)
2. Additional one Total required column (see picture). I don't have this data in dataset (i drew it in picture).
3. Adding 'TOTAL' in sum row (see picture)
4. Showing each student's transcript on separate page (I have them in one page now)
Thanks in advance
DATA have;
INFILE cards DSD missover;
INPUT Student_ID 1-3 Name $ 7-13 Category $ 17-25 Course_ID $ 31-37 Academic_Year $44-52 Credit 56-58
Grade $ 60-66 This_Year 67-69;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1
101 Michael English Eng2 2013-2014 1 89
101 Michael English Eng3 2012-2013 1 89
101 Michael Optional Sci2 2014-2015 .5 92 .5
101 Michael Optional CHIV 2014-2015 .25 92 .25
101 Michael Optional SSCV 2014-2015 1 92 1
101 Michael Science Sci1 2013-2014 1 86
101 Michael Group1 French1 2013-2014 1 86
101 Michael Group2 Human1 2013-2014 1 86
101 Michael Math Math1 2014-2015 1 89 1
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0
102 John Science Sci2 2014-2015 .5 70 .5
102 John Science Sci1 2014-2015 .5 60 .5
102 John Math Math1 2013-2014 1 78
102 John Math Math2 2013-2014 1 80
102 John INTERVIEW Int 2014-2015 0 Passeed0
102 John Optional Env1 2013-2014 1 82
102 John Group1 Human1 2013-2014 1 78
102 John English Eng1 2014-2015 1 80 1
102 John Group2 ESL 2012-2013 1 88
102 John English Eng2 2012-2013 1 82
;
RUN;
PROC REPORT DATA=have nofs HEADLINE HEADSKIP style(header)=[font_style=roman] /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
TITLE1 "STUDENT TRANSCRIPT";
COLUMN student_id Name category This_Year Credit;
DEFINE student_id / GROUP WIDTH=12;
DEFINE Name / GROUP;
DEFINE category / GROUP WIDTH=9 style=[font_face=courier];
DEFINE Credit / ANALYSIS SUM FORMAT=4.2 WIDTH=6 "Earned to Date";
DEFINE This_Year / ANALYSIS SUM FORMAT=4.2 WIDTH=6 "Earned This Year";
BREAK AFTER student_id / ol SUMMARIZE SKIP suppress style=[font_weight=bold];;
RUN;
1. remove extra red color rows (see attached) as they are just the duplicate of requirements (English requirement, earned this year and earned to date is appearing multiple times)
It would be easy by proc sort + nodupkey.
2. I want to get rid of Interview and Volunteer Hours (40 Hrs) row from the table (but I still beed the check box for this two item at the bottom)
It is a little complicated. You can't suppress some rows in proc report . I think you need create a macro for that.
DATA have;
INFILE cards expandtabs truncover;
INPUT Student_ID Name : $20. Group_Name & $20. Course_ID $
Academic_Year : $20. Credit
Grade $ This_Year Total_Required Group_Type & $40.;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year"
Total_Required="Total Required"
Group_Name="Group Name"
Group_Type="Group Type";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1 4 Compulsory Credits
101 Michael English Eng2 2013-2014 1 89 . 4 Compulsory Credits
101 Michael History Hist1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael English Eng3 2012-2013 1 89 . . Compulsory Credits
101 Michael Optional Sci2 2014-2015 .5 92 .5 3 Optional Credits
101 Michael Optional CHIV 2014-2015 .25 92 .25 3 Optional Credits
101 Michael Optional SSCV 2014-2015 1 92 1 3 Optional Credits
101 Michael Science Sci1 2013-2014 1 86 . 2 Compulsory Credits
101 Michael Geography Geog1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Group1 French1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Group2 Human1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Physical Education HPE1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Math Math1 2014-2015 1 89 1 2 Compulsory Credits
101 Michael INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John Science Sci2 2014-2015 .5 70 .5 2 Compulsory Credits
102 John Science Sci1 2014-2015 .5 60 .5 2 Compulsory Credits
102 John Math Math1 2013-2014 1 78 . 2 Compulsory Credits
102 John Math Math2 2013-2014 1 80 . 2 Compulsory Credits
102 John INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
102 John Optional Env1 2013-2014 1 82 . 3 Optional Credits
102 John Group1 Human1 2013-2014 1 78 . 1 Compulsory Credits
102 John History Hist1 2014-2015 1 88 4 Compulsory Credits
102 John VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John English Eng1 2014-2015 1 80 1 4 Compulsory Credits
102 John Group2 ESL 2012-2013 1 88 . 1 Compulsory Credits
102 John English Eng2 2012-2013 1 82 . 4 Compulsory Credits
;
RUN;
proc sort data=have nodupkey;by Group_Type Group_Name;run;
proc sort data=have;by student_id name;run;
proc sort data=have(keep=student_id name) out=key nodupkey;by student_id name;run;
%macro report(student_id=,name=);
ods escapechar='~';
OPTIONS NODATE pageno=1 nobyline;
%local x1 x2 y1 y2;
%let x1=;
%let x2=;
%let y1=;
%let y2=;
data _null_;
set have(where=(student_id=&student_id and name="&name")) end=last;
retain x y;
if Grade='Passeed' and Group_Type='Interview' then x=1;
if Grade='Done' and Group_Type='Volunteer Hours (40 hrs)' then y=1;
if last then do;
if x=1 then call symputx('x1','~{unicode 221A}','local');
else call symputx('x2','~{unicode 221A}','local');
if y=1 then call symputx('y1','~{unicode 221A}','local');
else call symputx('y2','~{unicode 221A}','local');
end;
run;
TITLE1 "Completion of Requirements for Graduation 2015/16";
TITLE2 "Report Period: Final";
TITLE3 j=r "Date: %sysfunc(today(),worddate.)";
title4 j=l "Student Id: &student_id Student Name: &name ";
footnote1 j=l "~S={asis=on}Interview Complete ~S={} &x1. Yes ~_~_ &x2. NO";
footnote2 j=l "~S={asis=on}Volunter Hours ~S={} &y1. Yes ~_~_ &y2. NO";
PROC REPORT DATA=have(where=(student_id=&student_id and name="&name" and
Group_Type not in ('Interview' 'Volunteer Hours (40 hrs)')))
nowd nofs HEADLINE HEADSKIP /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
COLUMN Group_Type Group_Name Total_Required This_Year Credit Grade;
DEFINE Group_Type / GROUP WIDTH=20 "Group Type";
DEFINE Group_Name / GROUP WIDTH=9;
DEFINE Total_Required / analysis WIDTH=9;
DEFINE This_Year / FORMAT=4.2 WIDTH=9 "Earned This Year";;
DEFINE Credit / FORMAT=4.2 WIDTH=9 "Earned to Date";
define Grade/noprint;
compute after ;
Group_Type='TOTAL';
endcomp;
rbreak after /summarize;
RUN;
%mend;
data _null_;
set key;
call execute(cats('%nrstr(%report(student_id=',student_id,',name=',name,'))'));
run;
Why is your subject line proc summary?
Hi: What is your destination of interest? Some of your options, such as OL, SKIP, HEADLINE, HEADSKIP and WIDTH are LISTING only options. Do you want HTML, RTF, PDF? Your use of a style override for a specific font implies that you are using ODS-- so do you want a destination that is a "paged" destination like RTF or PDF
PROC REPORT has both a BREAK statement and an RBREAK statement.
BREAK after STUDENT_ID/ summarize page;
would give you a summary line that summarized numeric variables and then would start a new page for every unique value of STUDENT_ID.
Not in LISTING, but in RTF and PDF you can simply turn on the date on the top right with the
OPTIONS DATE; statement or OPTIONS DATE NUMBER: if you want page numbering. If you want to put the date into HTML output, then you can do this:
title j=c "The Middle' j=r "&sysdate9.";
Most of what you asked for is a simple additon to the example you posted.
cynthia
DATA have;
INFILE cards DSD missover;
INPUT Student_ID 1-3 Name $ 7-13 Category $ 17-25 Course_ID $ 31-37 Academic_Year $44-52 Credit 56-58
Grade $ 60-66 This_Year 67-69;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1
101 Michael English Eng2 2013-2014 1 89
101 Michael English Eng3 2012-2013 1 89
101 Michael Optional Sci2 2014-2015 .5 92 .5
101 Michael Optional CHIV 2014-2015 .25 92 .25
101 Michael Optional SSCV 2014-2015 1 92 1
101 Michael Science Sci1 2013-2014 1 86
101 Michael Group1 French1 2013-2014 1 86
101 Michael Group2 Human1 2013-2014 1 86
101 Michael Math Math1 2014-2015 1 89 1
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0
102 John Science Sci2 2014-2015 .5 70 .5
102 John Science Sci1 2014-2015 .5 60 .5
102 John Math Math1 2013-2014 1 78
102 John Math Math2 2013-2014 1 80
102 John INTERVIEW Int 2014-2015 0 Passeed0
102 John Optional Env1 2013-2014 1 82
102 John Group1 Human1 2013-2014 1 78
102 John English Eng1 2014-2015 1 80 1
102 John Group2 ESL 2012-2013 1 88
102 John English Eng2 2012-2013 1 82
;
RUN;
data have;
set have(rename=(student_id=id));
student_id=put(id,8. -r);
drop id;
run;
PROC REPORT DATA=have nowd nofs HEADLINE HEADSKIP style(header)=[font_style=roman] /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
TITLE1 "STUDENT TRANSCRIPT";
TITLE2 j=r "Print Date: %sysfunc(today(),worddate.)";
COLUMN student_id Name category This_Year Credit total This_Year=_This_Year Credit=_Credit;
DEFINE student_id / GROUP WIDTH=12 style={just=r};
DEFINE Name / GROUP;
DEFINE category / GROUP WIDTH=9 style=[font_face=courier];
DEFINE Credit / ANALYSIS SUM FORMAT=4.2 WIDTH=6 "Earned to Date" noprint;
DEFINE This_Year / ANALYSIS SUM FORMAT=4.2 WIDTH=6 "Earned This Year" noprint;
define _This_Year/ FORMAT=4.2 WIDTH=6 "Earned This Year";
define _Credit/FORMAT=4.2 WIDTH=6 "Earned to Date";
define total/computed 'Total Required';
compute total;
total=sum(This_Year.sum,Credit.sum);
endcomp;
compute after student_id;
student_id='TOTAL';
endcomp;
BREAK AFTER student_id / ol SUMMARIZE SKIP suppress style=[font_weight=bold];;
RUN;
Hi Xia,
Thanks for your help. I did work on it a bit, but would you please help me to figure out few more things.
1. Student name and Student ID to appear on top of the document (see attached)
2. 'TOTAL' to go under group type.
3. Total Required field is a GROUP variable, is there a way to write the total at the bottom (I wrote it in the picture (18))? Total Required is a variable which explains how many course they have to complete from each group type.
4. Suppress 'Volunteer Hours (40 hrs)' & 'Interview' group type and put it inside the same table but after the 'TOTAL'. I doubt that such thing is possible, but I want to achieve at least the way I have shown in the picture. In either case, it should be associated with a Yes/No response (if 'Volunteer Hours (40 hrs)'=Done then 'Yes', If 'Interview'=Passed then 'Yes').
I really appreciate your help.
DATA have;
INFILE cards DSD missover;
INPUT Student_ID 1-3 Name $ 7-13 Group_Name $ 17-34 Course_ID $ 37-43 Academic_Year $50-58 Credit 62-64
Grade $ 66-72 This_Year 73-75 Total_Required 77-78 Group_Type $ 81-104;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year"
Total_Required="Total Required"
Group_Name="Group Name"
Group_Type="Group Type";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1 4 Compulsory Credits
101 Michael English Eng2 2013-2014 1 89 4 Compulsory Credits
101 Michael History Hist1 2014-2015 1 88 1 Compulsory Credits
101 Michael English Eng3 2012-2013 1 89 Compulsory Credits
101 Michael Optional Sci2 2014-2015 .5 92 .5 3 Optional Credits
101 Michael Optional CHIV 2014-2015 .25 92 .25 3 Optional Credits
101 Michael Optional SSCV 2014-2015 1 92 1 3 Optional Credits
101 Michael Science Sci1 2013-2014 1 86 2 Compulsory Credits
101 Michael Geography Geog1 2014-2015 1 88 1 Compulsory Credits
101 Michael Group1 French1 2013-2014 1 86 1 Compulsory Credits
101 Michael Group2 Human1 2013-2014 1 86 1 Compulsory Credits
101 Michael Physical Education HPE1 2014-2015 1 88 1 Compulsory Credits
101 Michael Math Math1 2014-2015 1 89 1 2 Compulsory Credits
101 Michael INTERVIEW Int 2014-2015 0 Passeed0 0 Interview
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John Science Sci2 2014-2015 .5 70 .5 2 Compulsory Credits
102 John Science Sci1 2014-2015 .5 60 .5 2 Compulsory Credits
102 John Math Math1 2013-2014 1 78 2 Compulsory Credits
102 John Math Math2 2013-2014 1 80 2 Compulsory Credits
102 John INTERVIEW Int 2014-2015 0 Passeed0 0 Interview
102 John Optional Env1 2013-2014 1 82 3 Optional Credits
102 John Group1 Human1 2013-2014 1 78 1 Compulsory Credits
102 John History Hist1 2014-2015 1 88 4 Compulsory Credits
101 John VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John English Eng1 2014-2015 1 80 1 4 Compulsory Credits
102 John Group2 ESL 2012-2013 1 88 1 Compulsory Credits
102 John English Eng2 2012-2013 1 82 4 Compulsory Credits
;
RUN;
data have;
LABEL Student_ID="Student ID";
set have(rename=(student_id=id));
student_id=put(id,8. -r);
drop id;
run;
OPTIONS NODATE pageno=1;
PROC REPORT DATA=have nowd nofs HEADLINE HEADSKIP /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
TITLE1 "Completion of Requirements for Graduation 2015/16";
TITLE2 "Report Period: Final";
TITLE3 j=r "Date: %sysfunc(today(),worddate.)";
COLUMN student_id Group_Type Group_Name Total_Required This_Year Credit;
DEFINE student_id / GROUP WIDTH=12 style={just=r};
DEFINE Group_Type / GROUP WIDTH=20 "Group Type";
DEFINE Group_Name / GROUP WIDTH=9;
DEFINE Total_Required / GROUP WIDTH=9;
DEFINE This_Year / FORMAT=4.2 WIDTH=9 "Earned This Year";;
DEFINE Credit / FORMAT=4.2 WIDTH=9 "Earned to Date";;
compute after student_id;
Student_ID='TOTAL';
endcomp;
BREAK AFTER Student_ID / ol SUMMARIZE page suppress style=[font_weight=bold];;
RUN;
DATA have;
INFILE cards expandtabs truncover;
INPUT Student_ID Name : $20. Group_Name & $20. Course_ID $
Academic_Year : $20. Credit
Grade $ This_Year Total_Required Group_Type & $40.;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year"
Total_Required="Total Required"
Group_Name="Group Name"
Group_Type="Group Type";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1 4 Compulsory Credits
101 Michael English Eng2 2013-2014 1 89 . 4 Compulsory Credits
101 Michael History Hist1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael English Eng3 2012-2013 1 89 . . Compulsory Credits
101 Michael Optional Sci2 2014-2015 .5 92 .5 3 Optional Credits
101 Michael Optional CHIV 2014-2015 .25 92 .25 3 Optional Credits
101 Michael Optional SSCV 2014-2015 1 92 1 3 Optional Credits
101 Michael Science Sci1 2013-2014 1 86 . 2 Compulsory Credits
101 Michael Geography Geog1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Group1 French1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Group2 Human1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Physical Education HPE1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Math Math1 2014-2015 1 89 1 2 Compulsory Credits
101 Michael INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John Science Sci2 2014-2015 .5 70 .5 2 Compulsory Credits
102 John Science Sci1 2014-2015 .5 60 .5 2 Compulsory Credits
102 John Math Math1 2013-2014 1 78 . 2 Compulsory Credits
102 John Math Math2 2013-2014 1 80 . 2 Compulsory Credits
102 John INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
102 John Optional Env1 2013-2014 1 82 . 3 Optional Credits
102 John Group1 Human1 2013-2014 1 78 . 1 Compulsory Credits
102 John History Hist1 2014-2015 1 88 4 Compulsory Credits
102 John VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John English Eng1 2014-2015 1 80 1 4 Compulsory Credits
102 John Group2 ESL 2012-2013 1 88 . 1 Compulsory Credits
102 John English Eng2 2012-2013 1 82 . 4 Compulsory Credits
;
RUN;
proc sort data=have;by student_id name;run;
ods escapechar='~';
OPTIONS NODATE pageno=1 nobyline;
PROC REPORT DATA=have nowd nofs HEADLINE HEADSKIP /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
TITLE1 "Completion of Requirements for Graduation 2015/16";
TITLE2 "Report Period: Final";
TITLE3 j=r "Date: %sysfunc(today(),worddate.)";
title4 j=l "Student Name: #byval1 Student Id: #byval2";
by student_id name;
COLUMN Group_Type Group_Name Total_Required This_Year Credit Grade;
DEFINE Group_Type / GROUP WIDTH=20 "Group Type";
DEFINE Group_Name / GROUP WIDTH=9;
DEFINE Total_Required / analysis WIDTH=9;
DEFINE This_Year / FORMAT=4.2 WIDTH=9 "Earned This Year";;
DEFINE Credit / FORMAT=4.2 WIDTH=9 "Earned to Date";
define Grade/noprint;
compute Grade;
if Grade='Passeed' and Group_Type='Interview' then X=1;
if Grade='Done' and Group_Type='Volunteer Hours (40 hrs)' then Y=1;
endcomp;
compute after /style(lines)={just=left};
Group_Type='TOTAL';
if X=1 then XX1='~{unicode 221A}';
else XX2='~{unicode 221A}';
if Y=1 then YY1='~{unicode 221A}';
else YY2='~{unicode 221A}';
line 'Interview Complete ' XX1 $16. 'Yes ' XX2 $16. ' NO';
line 'Volunter Hours ' YY1 $16. 'Yes ' YY2 $16. ' NO' ;
endcomp;
rbreak after /summarize;
RUN;
Hi Xia,
Thanks for your help to get me closer to what I want. I still have to figure out how to:
1. remove extra red color rows (see attached) as they are just the duplicate of requirements (English requirement, earned this year and earned to date is appearing multiple times)
2. I want to get rid of Interview and Volunteer Hours (40 Hrs) row from the table (but I still beed the check box for this two item at the bottom)
Would you pease help me a bit more to get what I want. Thanks.
1. remove extra red color rows (see attached) as they are just the duplicate of requirements (English requirement, earned this year and earned to date is appearing multiple times)
It would be easy by proc sort + nodupkey.
2. I want to get rid of Interview and Volunteer Hours (40 Hrs) row from the table (but I still beed the check box for this two item at the bottom)
It is a little complicated. You can't suppress some rows in proc report . I think you need create a macro for that.
DATA have;
INFILE cards expandtabs truncover;
INPUT Student_ID Name : $20. Group_Name & $20. Course_ID $
Academic_Year : $20. Credit
Grade $ This_Year Total_Required Group_Type & $40.;
LABEL Student_ID="Student ID"
Course_ID="Course ID"
Academic_Year="Academic Year"
This_Year="This Year"
Total_Required="Total Required"
Group_Name="Group Name"
Group_Type="Group Type";
DATALINES;
101 Michael English Eng1 2014-2015 1 80 1 4 Compulsory Credits
101 Michael English Eng2 2013-2014 1 89 . 4 Compulsory Credits
101 Michael History Hist1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael English Eng3 2012-2013 1 89 . . Compulsory Credits
101 Michael Optional Sci2 2014-2015 .5 92 .5 3 Optional Credits
101 Michael Optional CHIV 2014-2015 .25 92 .25 3 Optional Credits
101 Michael Optional SSCV 2014-2015 1 92 1 3 Optional Credits
101 Michael Science Sci1 2013-2014 1 86 . 2 Compulsory Credits
101 Michael Geography Geog1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Group1 French1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Group2 Human1 2013-2014 1 86 . 1 Compulsory Credits
101 Michael Physical Education HPE1 2014-2015 1 88 . 1 Compulsory Credits
101 Michael Math Math1 2014-2015 1 89 1 2 Compulsory Credits
101 Michael INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
101 Michael VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John Science Sci2 2014-2015 .5 70 .5 2 Compulsory Credits
102 John Science Sci1 2014-2015 .5 60 .5 2 Compulsory Credits
102 John Math Math1 2013-2014 1 78 . 2 Compulsory Credits
102 John Math Math2 2013-2014 1 80 . 2 Compulsory Credits
102 John INTERVIEW Int 2014-2015 0 Passeed 0 0 Interview
102 John Optional Env1 2013-2014 1 82 . 3 Optional Credits
102 John Group1 Human1 2013-2014 1 78 . 1 Compulsory Credits
102 John History Hist1 2014-2015 1 88 4 Compulsory Credits
102 John VOL_HOUR Vol 2014-2015 0 Done 0 0 Volunteer Hours (40 hrs)
102 John English Eng1 2014-2015 1 80 1 4 Compulsory Credits
102 John Group2 ESL 2012-2013 1 88 . 1 Compulsory Credits
102 John English Eng2 2012-2013 1 82 . 4 Compulsory Credits
;
RUN;
proc sort data=have nodupkey;by Group_Type Group_Name;run;
proc sort data=have;by student_id name;run;
proc sort data=have(keep=student_id name) out=key nodupkey;by student_id name;run;
%macro report(student_id=,name=);
ods escapechar='~';
OPTIONS NODATE pageno=1 nobyline;
%local x1 x2 y1 y2;
%let x1=;
%let x2=;
%let y1=;
%let y2=;
data _null_;
set have(where=(student_id=&student_id and name="&name")) end=last;
retain x y;
if Grade='Passeed' and Group_Type='Interview' then x=1;
if Grade='Done' and Group_Type='Volunteer Hours (40 hrs)' then y=1;
if last then do;
if x=1 then call symputx('x1','~{unicode 221A}','local');
else call symputx('x2','~{unicode 221A}','local');
if y=1 then call symputx('y1','~{unicode 221A}','local');
else call symputx('y2','~{unicode 221A}','local');
end;
run;
TITLE1 "Completion of Requirements for Graduation 2015/16";
TITLE2 "Report Period: Final";
TITLE3 j=r "Date: %sysfunc(today(),worddate.)";
title4 j=l "Student Id: &student_id Student Name: &name ";
footnote1 j=l "~S={asis=on}Interview Complete ~S={} &x1. Yes ~_~_ &x2. NO";
footnote2 j=l "~S={asis=on}Volunter Hours ~S={} &y1. Yes ~_~_ &y2. NO";
PROC REPORT DATA=have(where=(student_id=&student_id and name="&name" and
Group_Type not in ('Interview' 'Volunteer Hours (40 hrs)')))
nowd nofs HEADLINE HEADSKIP /*nofs is used to turn off the procedure’s interactive features*/
OUT=reportout;
COLUMN Group_Type Group_Name Total_Required This_Year Credit Grade;
DEFINE Group_Type / GROUP WIDTH=20 "Group Type";
DEFINE Group_Name / GROUP WIDTH=9;
DEFINE Total_Required / analysis WIDTH=9;
DEFINE This_Year / FORMAT=4.2 WIDTH=9 "Earned This Year";;
DEFINE Credit / FORMAT=4.2 WIDTH=9 "Earned to Date";
define Grade/noprint;
compute after ;
Group_Type='TOTAL';
endcomp;
rbreak after /summarize;
RUN;
%mend;
data _null_;
set key;
call execute(cats('%nrstr(%report(student_id=',student_id,',name=',name,'))'));
run;
Oh, I just realize you need add two more variables into PROC SORT. I must forget them due to too rush .
proc sort data=have nodupkey;by Student_ID Name Group_Type Group_Name;run;
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.