How to keep the same table structure in proc report?

Reply
Occasional Contributor
Posts: 6

How to keep the same table structure in proc report?

Hi all,

I am a new SAS user, and recently encountered a task that I have been trying for a while to solve (no luck though). 

I have created a mock example below.

If I have a SAS dataset like this:

Course             Name               Grade

Math                Jen                      A

Accounting      Jen                      A

English            Jen                      A

Biology            Jen                       A

Math                Ben                       B

English            Ben                      A

Accounting      Clark                    C

Biology             Clark                  C

I would like to have 3 reports (ods output to PDF) per student.  When I use the following codes,

ods pdf ...

proc report data=reportcard ;

column course grade;

define course /  xxxx ;

define grade /  xxxx ;

where student=&name;

….

The results are:

Name: Jen

Course

Grade

Math

A

Accounting

A

English

A

Biology

A

Name: Ben

Course

Grade

Math

B

English

A

Name: Clark

Course

Grade

Accounting

C

Biology

C

However, what I really want the output to be is:

Name: Jen

Course

Grade

Math

A

Accounting

A

English

A

Biology

A

Name: Ben

Course

Grade

Math

B

Accounting

N/A

English

A

Biology

N/A

Name: Clark

Course

Grade

Math

N/A

Accounting

C

English

N/A

Biology

C

So my questions are: 

(1)  What codes I can use to achieve the desired table structure?

(2)  How to put N/A in the grade for those missing courses for Ben and Clark

For a small dataset, it may be possible to find out what courses/grades are missing and create those observations in the SAS dataset.

However, what if this is for large data with more than 20 courses and more than 100 students?


Thank you for your help.

Super User
Posts: 11,343

Re: How to keep the same table structure in proc report?

I don't use REPORT much but you might look up PRELOADFMT for use in the define part of an across variable. This would require creating a format for course, basically one that looks like

proc format;

value $course

'Math' = 'Math'

'English'='English'

(etc for all courses)

;

run;

preloadfmt then loads all of the values of the format including those that have no values there are some requirements such as use of COMPLETEROWS on the proc statment and use of ORDER=DATA for your needs. Obviously the format needs to be assigned to the variable as well.

The second part of your question would also be solved by a format. You don't say if the grades are numeric or string but the idea would be if string:

value $grade

'A' = 'A'

'F' = 'F'

'',' ' = 'N/A'

;

if numeric something like

value grade

91 - 100= 'A'

81 -  90 = 'B'

0 - 60 = 'F'

. = 'N/A'

;

Occasional Contributor
Posts: 6

Re: How to keep the same table structure in proc report?

Hi ballardw,

Thank you for your reply.  I think this way may work - I'll give it a try.

I actually found out that I can use PROC SQL to create the datasets the way I desired, and use the IF statement to insert N/A to those missing the departments.


Thanks,

Ask a Question
Discussion stats
  • 2 replies
  • 204 views
  • 3 likes
  • 2 in conversation