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?
Try proc report: group by student and course, sum for credit and across for n_grade.
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;
@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!
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.
@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
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;
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!!
Give a try and happy to help. Look into a COMPUTE statement with a BREAK statement as well.
https://excursive.org/sas/ProcReportPaper.pdf
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.