topic Re: Write all credits and grades on 1 row for each student in SAS Programming

Write all credits and grades on 1 row for each student

SAS_Question — Tue, 16 Aug 2022 08:37:01 GMT

I HAVE (see the import file have.csv) and this code to import it:

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

	format ID $20.;
	format COURSE $20.;
	format DATE YYMMDD10.;
	format EARNED_CREDIT $20.;
	format CRS_COMP $20.;
	format C_GRADE $20.;
	format N_GRADE 4.;
	format CREDIT 4.;

	input
	ID
	COURSE 
	DATE 
	EARNED_CREDIT
	CRS_COMP
	C_GRADE
	N_GRADE
	CREDIT
;
run;

I WANT:

The Credits (TotalCreditEarned) and ALL Grades (as (chance_1,Chance_2,Chance_n) in each column apart) of each student to show up like this in a proc print.

How do I do this?

Re: Write all credits and grades on 1 row for each student

andreas_lds — Tue, 16 Aug 2022 10:26:29 GMT

Try proc report: group by student and course, sum for credit and across for n_grade.

Re: Write all credits and grades on 1 row for each student

Ksharp — Tue, 16 Aug 2022 12:30:20 GMT

data work.HAVE;
/* change path to fit your setup! */
  infile "c:\temp\have.csv" 
 delimiter = "|"
 missover 
 dsd
 firstobs=2;
 
 informat ID $20.;
 informat COURSE $20.;
 informat DATE DDMMYY10.;
 informat EARNED_CREDIT $20.;
 informat CRS_COMP $20.;
 informat C_GRADE $20.;
 informat N_GRADE 4.;
 informat CREDIT 4.;

 format ID $20.;
 format COURSE $20.;
 format DATE YYMMDD10.;
 format EARNED_CREDIT $20.;
 format CRS_COMP $20.;
 format C_GRADE $20.;
 format N_GRADE 4.;
 format CREDIT 4.;

 input
 ID
 COURSE 
 DATE 
 EARNED_CREDIT
 CRS_COMP
 C_GRADE
 N_GRADE
 CREDIT
;
run;

data part1(index=(x=(id course)));
 set have;
 if CREDIT=0;
run;
proc transpose data=part1 out=part2(drop=_: course) prefix=Chance_;
by id course;
var C_GRADE;
run;

data part3(index=(x=(id course)) rename=(CREDIT=TotalCreditEarned));
 set have;
 if CREDIT ne 0;
 keep id course CREDIT;
run;


data want;
 merge part3 part2;
 by id;
run;

Re: Write all credits and grades on 1 row for each student

SAS_Question — Tue, 16 Aug 2022 16:28:56 GMT

@Ksharp .. you really deserve a diamond as sharp as your Knowledge for your programming skills! Wow!! 🙂

I'm waiting with accepting your working solution though.

Because I'm really eager to learn more about proc report and how to solve this HAVE data of mine with a single Proc Report...

Thanks for your answer though. That's a really neat solution too!!!!

@andreas_lds thank you for the tip. Can you give an example of this Proc Report you are mentioning? Because I'm still learning this proc I would really love to see an example of how you would do it. Thanks a lot in advance!

Re: Write all credits and grades on 1 row for each student

Reeza — Tue, 16 Aug 2022 16:52:43 GMT

The solution depends on what type of output you want. If you want only displayed output, piped to Excel/PDF/WORD use PROC REPORT. If you want it in that format in a data set to do further analysis, then @Ksharp solution is the one you want.

Re: Write all credits and grades on 1 row for each student

SAS_Question — Tue, 16 Aug 2022 17:58:38 GMT

I would love to see the first output you mentioned @Reeza !! Thank you for your clarification on this matter!

Re: Write all credits and grades on 1 row for each student

PaigeMiller — Tue, 16 Aug 2022 21:10:45 GMT

@SAS_Question wrote:
I would love to see the first output you mentioned @Reeza !! Thank you for your clarification on this matter!

Examples of using ACROSS in PROC REPORT.

https://communities.sas.com/t5/SAS-Procedures/Proc-report-ACROSS/td-p/433732

Re: Write all credits and grades on 1 row for each student

Reeza — Tue, 16 Aug 2022 21:12:25 GMT

Here's a starter. Based on this, I suspect you have to do some more data wrangling to get the PROC REPORT exactly correct.

FYI - I had to change the import step to correctly read the data, the Date informat was incorrect and c_grade was being read in as a character when it should be numeric. I'm assuming....

data work.HAVE;
/* change path to fit your setup! */
  infile "/home/fkhurshed/Demo1/HAVE.csv" 
	delimiter = "|"
	missover 
	dsd
	firstobs=2;
 
	informat ID $20.;
	informat COURSE $20.;
	informat DATE DDMMYY10.;
	informat EARNED_CREDIT $20.;
	informat CRS_COMP $20.;
	informat C_GRADE commax.;
	informat N_GRADE 4.;
	informat CREDIT 4.;

	format ID $20.;
	format COURSE $20.;
	format DATE YYMMDD10.;
	format EARNED_CREDIT $20.;
	format CRS_COMP $20.;
	format C_GRADE 8.2;
	format N_GRADE 4.;
	format CREDIT 4.;

	input
	ID
	COURSE 
	DATE 
	EARNED_CREDIT
	CRS_COMP
	C_GRADE
	N_GRADE
	CREDIT
;
run;

proc sort data=have;
by id course;
run;

data have_indexed;
set have;
by id course;
*where credit ne 0;
if first.course or first.id then chance=1;
else chance+1;
run;


proc report data=have_indexed;
*where credit ne 0;
column ID course credit (chance , c_grade)  ;
define id / group;
define course /group;
define chance / across ;
define credit / sum;
define c_grade / '' sum format=8.1;
run;

Re: Write all credits and grades on 1 row for each student

SAS_Question — Wed, 17 Aug 2022 11:28:01 GMT

Thank you @Reeza !!! That's what I mean. Only... 1 addition please..if you don't mind..?

How do I get a sum (of 25 CREDITS for each student) on these 2 places of the CREDITS? Can you point me out in the right direction? TiA!!

Re: Write all credits and grades on 1 row for each student

Reeza — Wed, 17 Aug 2022 14:00:14 GMT

Give a try and happy to help. Look into a COMPUTE statement with a BREAK statement as well.

https://excursive.org/sas/ProcReportPaper.pdf