The SAS Output Delivery System and reporting techniques

PROC REPORT some help

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

PROC REPORT some help

[ Edited ]

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;
Attachment

Accepted Solutions
Solution
‎06-30-2016 11:11 AM
Grand Advisor
Posts: 9,567

Re: PROC REPORT some help

[ Edited ]

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;

 

x.png

View solution in original post


All Replies
Grand Advisor
Posts: 17,287

Re: PROC SUMMARY some help

Why is your subject line proc summary?

Regular Contributor
Posts: 215

Re: PROC SUMMARY some help

Thanks Reeza.
SAS Super FREQ
Posts: 8,716

Re: PROC REPORT some help

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

 

  

 

 

  

Regular Contributor
Posts: 215

Re: PROC REPORT some help

Thanks Cynthia, Your suggestion was very helpful.
Grand Advisor
Posts: 9,567

Re: PROC REPORT some help

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;

x.png

Regular Contributor
Posts: 215

Re: PROC REPORT some help

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;
Attachment
Grand Advisor
Posts: 9,567

Re: PROC REPORT some help

[ Edited ]

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;

 

x.png

Regular Contributor
Posts: 215

Re: PROC REPORT some help

[ Edited ]

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.

Attachment
Solution
‎06-30-2016 11:11 AM
Grand Advisor
Posts: 9,567

Re: PROC REPORT some help

[ Edited ]

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;

 

x.png

Regular Contributor
Posts: 215

Re: PROC REPORT some help

Thanks for your help Xia. It now worked for me. I really appreciate your help.
Grand Advisor
Posts: 9,567

Re: PROC REPORT some help

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 553 views
  • 3 likes
  • 4 in conversation