topic Re: Question for Proc Report of Across with different number of obs in ODS and Base Reporting

Question for Proc Report of Across with different number of obs

jsbyxws — Wed, 29 Aug 2018 02:13:54 GMT

Not sure how to do proc report with across. I have a sample dataset like below for school, Math score, English score:

School1 91 91
School1 92 92
School1 93 93
School2 81 81
School2 82 82
School3 71 71

I want to proc report with school across and show in new page, i.e.

in page1:

School1

math English

91 91

92 92

93 93

in a new page (here page 2 since page1 already has all for school1. In reality if school1 has more pages, school2 always start at new page):

School2

math English

81 81

82 82

similary for school3 in a new page like below:

School3

math English

71 71

Re: Question for Proc Report of Across with different number of obs

Ksharp — Wed, 29 Aug 2018 11:59:54 GMT

CODE NOT TEST

options nobyline;

title ' #byval1 ';

proc report data=have nowd;

by school;

column math english;

run;

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Wed, 29 Aug 2018 13:16:52 GMT

Ksharp,

Thanks a lot for the code! I tested and output to pdf. It did start a new page for each school. But there is only one line for total (I guess it uses SUM). Is there any option to list them as it is? BTW, before proc report, I did add proc sort if you happen to use the code to test.

Re: Question for Proc Report of Across with different number of obs

Cynthia_sas — Wed, 29 Aug 2018 14:52:51 GMT

Hi:
Since MATH and ENGLISH are numeric, their default usage is ANALYSIS with a STATISTIC of SUM. So if you want each row to be displayed, you have to provide explicit DEFINE statements:
DEFINE Math / display;
DEFINE English / display;

In this case, you are NOT using ACROSS items at all.

Cynthia

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Wed, 29 Aug 2018 21:12:11 GMT

Cynthia,

Thanks a lot for help! It works! As you said "across" pages is not "across any more. So define instead of analysis will work.

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Thu, 30 Aug 2018 14:59:35 GMT

Got another question about proc report with by variable. This is the sample SAS program:

data have;
input SchoolN Math $ English $;
datalines;
1 Albert Alice
1 Adam Amanda
2 Ben Brenda
;
run;

data have;
set have;
label SchoolN="School Name";
run;

proc format;
value SchoolN 1="School 1" 2="School 2" 3="School 3";
run;

proc report data=have;
by schoolN;
format schoolN schoolN.;

column Math English;

define Math /display;
define English /display;
run;

With by variable, it DOES print in a new page for each school. But the school shows up as "School Name=School 1" for page 1 and "School Name=School 2" for page 2. How to remove "School Name="?

BTW, when I insert title '#byval1'; inside proc report, it works. Now "School Name=" is gone.

Hi Ksharp and Cythia, thanks! I saw your other post for above solution.

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Thu, 30 Aug 2018 16:10:08 GMT

Hi, I am now stuck with another issue in proc report with by variable: my column header depends on "by variable", how to show them?

Assume original data has 3 columns: school number, honor students, number of students at school.

data have;
input SchoolN Math $ N;
datalines;
1 Albert 100
1 Adam 100
2 Ben 200
;
run;

I want to show in proc report each school starting a new page. I got it wit by SchoolN. But I also want to show header for Math total number of students for that school, i.e. column header depends on by variable. How to do it?

I want the report for each school to show

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Thu, 30 Aug 2018 18:43:33 GMT

Just consulted with some SAS guru and found the detour solution.

Technically in ONE proc report, you cannot use a macro in header if it depends on the by variable! But there are solutions to this: use multiple proc reports one after another. They will show up in ONE pdf/rtf file. Each proc report will use "title '#byval1';" with by variable in proc report. In each proc report, column header will use macro variable which can be explicitly called (of course you have to create them first for each value of by variable).

In this example, need to create 2 macro variables for two schools, say :N1=100 and :N2=200. Then &N1 will be used in column header for proc report with SchoolN=1. Likewise for &N2.

Re: Question for Proc Report of Across with different number of obs

Ksharp — Fri, 31 Aug 2018 13:26:16 GMT

data have;
    input SchoolN Math $ N;
datalines;
1 Albert 100
1 Adam 100
2 Ben 200
;
run;

options nobyline;
title 'school is #byval1';
proc report data=have nowd;
by schoolN;
column math n;
define math/display;
define N/analysis sum;

compute before _page_;
line 'School  has total  '  n.sum;
endcomp;
run;

Re: Question for Proc Report of Across with different number of obs

jsbyxws — Fri, 07 Sep 2018 02:20:06 GMT

Thanks very much!