Proc report or proc tabulate to fix the job - help needed

SAS_Question — Sat, 13 Aug 2022 22:53:49 GMT

Please see the attachment for the data I use in HAVE!

data work.HAVE;
/* change path to fit your setup! */
  infile "D:\GRADES\import.txt" 
	delimiter = "|"
	missover 
	dsd
	firstobs=2;
 
	informat ID $20.;
	informat CAT_NR $20.;
	informat DATE YYMMDD10.;
	informat EARNED_CREDIT $20.;
	informat CAT_COMPONENT $20.;
	informat C_GRADE $20.;
	informat N_GRADE 4.;
	informat CREDIT 4.;

	format ID $20.;
	format CAT_NR $20.;
	format DATE YYMMDD10.;
	format EARNED_CREDIT $20.;
	format CAT_COMPONENT $20.;
	format C_GRADE $20.;
	format N_GRADE 4.;
	format CREDIT 4.;

	input
	ID
	CAT_NR 
	DATE 
	EARNED_CREDIT
	CAT_COMPONENT
	C_GRADE
	N_GRADE
	CREDIT
;
run;

I HAVE this fictive students grades data.
This is how it works:

A grade will get the corresponding credit (of 0, 5, 10, 15 etc.) if it is 5.5 or higher.
If a grade is lower than 5.5 it will not get a credit(registration) in the data!

If a student gets a grade for a CAT_COMPONENT other than 'CRED', then that grade will always
be registered on that corresponding CAT_NR (ending with xxxxEXAM/xxxPROJ/etc.)
Even if this grade is lower than 5.5!
Please note: the CAT_NR ones ending with xxxCRED -> are not EXAMS/PROJ/CASE/etc,
those are only used for registering credits (0, 5, 10, 15 etc).

The normal case is that students make exams, projects, cases, etc, and get graded for that.
But sometimes there are students with exemptions. Meaning they have already did that exam in other school etc.
They get credited without making these exams/projects/cases again.
In these cases:
If it is an exemption credit (meaning C_GRADE='FR') then it is directly credited on CRED (without an EXAM grade) and the N_grade is always: 6 in these cases.
If it is another credit with some value/meaning (C_Grade='A'), then it has always N_Grade=8.
If it is another credit without some value/meaning (C_Grade='-'), then it has always N_Grade=0.

Let me give an example:
--------------------------------------------- begin of example!
If student '29A86T8' gets in his first chance a grade of 3.5 for CAT_NR 'BA0AM106EXAM' in the year 2021,
and a grade of 5.7 in his next chance in the year of 2022,
and he got an exemption for cat_nr: 'PA01300PACRED' (with CRED value of 10)
he will be registered like these 4 (!) rows in the table of HAVE:

OBS|ID|CAT_NR|DATE|EARNED_CREDIT|CAT_COMPONENT|C_GRADE|N_GRADE|CREDIT

1|29A86T8|BA0AM106EXAM|2021-06-16|N|EXAM|3,5|3.5|0
(please note: in this case he gets no credit registration, because the grade is lower than 5.5!)

2|29A86T8|BA0AM106EXAM|2022-06-16|Y|EXAM|5,7|5.7|5
3|29A86T8|BA0AM106CRED|2022-06-16|Y|CRED|5,7|5.7|5
4|29A86T8|PA01300PACRED|2022-06-16|Y|CRED|FR|6|10

(please note: in this case the grade is 5.5 or higher so he gets the credit = earned_credit='Y'!
please note also the exemption how it is registered. )

So in this case student 29A86T8 got his CREDIT eventually in the year 2022 with 2 chances for the Cat_NR:
BA0AM106EXAM
And he got the credit for PA01300PACRED because of his exemption.

So he did the exam 2 times before succeeding for this exam.
And he used 1 exemption.
----------------------------------------------------- end of example!

Now I hope you understand my data better? 🙂
If yes, then this is what I WANT:

To learn a bit more about proc report/proc tabulate,
I want some help with this specific procs used.
Can someone help out and show me how to show this with the help of proc report or tabulate?

WANT, something like this:

So 1 proc report/tabulate result showing:
all grades in columns, and also a min_grade, max_grade, avg_grade, N_grade(=how many chances until succeeded?), sum_of_CREDITS
this should be all:
-per year,
-per student(ID),
-per CAT_NR (so if there are 2 chances like above we should see only 1 row summing all the grades in 1 row not 2 for the same CAT_NR).
And please note: we want to show only the CAT_NR which are ending with CRED (so we need to merge alle the grades in to CRED)!

In the end I want to show 1 row for each student for each year for each Cat_NR.
Is that possible with proc report?
Can someone help out? Thanks in advance!

Re: Proc report or proc tabulate to fix the job - help needed

Patrick — Sat, 13 Aug 2022 23:26:41 GMT

Highly appreciated that you provided sample data with fully working code.

Given the layout you're showing I'd certainly go for Proc Report as it allows for more flexibility than Proc Tabulate.

I suggest you invest a bit of time investigating how Proc Report works. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p0bqogcics9o4xn17yvt2qjbgdpi.htm

I'm myself not a frequent report creator and though not that "fluent" with Proc Report. If I have to create a bit more advanced reports then I normally spend some time Googling for examples (it's much out there) and then use the most fitting sample code as my starting point.

Re: Proc report or proc tabulate to fix the job - help needed

SAS_Question — Sun, 14 Aug 2022 10:31:28 GMT

I appreciate your answer and tip @Patrick, but I'm really a rookie in proc report.
Like in my OP: what I want is some help with this.
I really need some worked out example for my data, so I can get going with this and explore the proc report more and more, like you say.
But thanks anyway for your reaction!

topic Re: Proc report or proc tabulate to fix the job - help needed in SAS Programming

Proc report or proc tabulate to fix the job - help needed

Re: Proc report or proc tabulate to fix the job - help needed

Re: Proc report or proc tabulate to fix the job - help needed