Proc Report - two across variables

[Dave25]

I've got summary data, trying to create a report with Proc Report, with two Across variables.

i've included some stipped down sample data - i can create two reports separately, but can't figure out how to put them together.

any suggestions would be greatly appreaciated!

Dave

data a;
input grade type1 $ count1 type2a $ type2b $ count2;
cards;
1 A1 1 . . .
1 B1 1 . . .
1 C1 1 . . .
1 . . A2 A2a 1
1 . . A2 A2b 1
;
run;

title1 'type1 - works fine';
proc report data=a;
columns ("Grade" grade) type1,(count1);
define grade/' ' group;
define type1/' ' across;
define count1/' ' sum;
run;

title1 'type2a/type2b - works fine';
proc report data=a;
columns ("Grade" grade) type2a, type2b,(count2);
define grade/' ' group;
define type2a/' ' across;
define type2b/' ' across;
define count2/' ' sum;
run;

title1 "Doesn't Work";
proc report data=a;
columns ("Grade" grade)  (type1,(count1))  (type2a, type2b,(count2));
define grade/' ' group;
define type1/' ' across;
define count1/' ' sum;
define type2a/' ' across;
define type2b/' ' across;
define count2/' ' sum;
run;

[ballardw]

Can you show what you would like the final single report table to look like? As it is we don't really know what you want.

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results.

[Dave25]

Apologies - i was hoping to get this result:

				A2
Grade	A1	B1	C1	A2a	A2b
1	1	1	1	1	1

[Dave25]

"Provide input data in the form of data step code pasted into a code box"

data a;
input grade type1 $ count1 type2a $ type2b $ count2;
cards;
1 A1 1 . . .
1 B1 1 . . .
1 C1 1 . . .
1 . . A2 A2a 1
1 . . A2 A2b 1
;
run;

Here's the log from the 3rd Proc Report (the one that doesn't work:

82   title1 "Doesn't Work";
83   proc report data=a;
84   columns ("Grade" grade)  (type1,(count1))  (type2a, type2b,(count2));
85   define grade/' ' group;
86   define type1/' ' across;
87   define count1/' ' sum;
88   define type2a/' ' across;
89   define type2b/' ' across;
90   define count2/' ' sum;
91   run;

WARNING: A GROUP, ORDER, or ACROSS variable is missing on every observation.
NOTE: There were 5 observations read from the data set WORK.A.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds

[ballardw]

This almost works:

proc tabulate data=a;
   class grade type1 type2a type2b /missing;
   var count1 count2;
   table grade,
         type1=''*count1=''*sum=''*f=best5.
         type2a=''*type2b=''*count2=''*sum=''*f=best5.
        /misstext=' ' row=float
   ;
run;

Proc tabulate is creating some extra columns because we have to allow missing values for the categories. The ='' are to suppress variable name or label from appearing.

Often the WARNING about missing group, order or across variables (in Report) or class variables (in Proc tabulate) is an indication of possibly poor data structure

Consider

data example;
   input grade group $ subgroup $ count;
datalines;
1  1 A 13
1  1 B 4
1  1 C 12
1  2 Aa 14
1  2 Ab  9
;
run;

proc tabulate data= example;
   class grade group subgroup;
   var count;
   table grade,
         group*subgroup=''*count=''*sum=''*f=best5.
         ;
run;

[Dave25 · Accepted solution]

thanks for the suggestions - continued hunting around, and finally found a solution:

add MISSING in the Proc Report line, and NOZERO in the across lines

data a;
input grade type1 $ count1 type2a $ type2b $ count2;
cards;
1 A1 1 . . .
1 B1 1 . . .
1 C1 1 . . .
1 . . A2 A2a 1
1 . . A2 A2b 1
;
run;

proc report data=a missing;
columns ("Grade" grade)  (type1,(count1))  (type2a, type2b,(count2));
define grade/' ' group;
define type1/' ' nozero across;
define count1/' ' sum;
define type2a/' ' nozero  across;
define type2b/' ' nozero across;
define count2/' ' sum;
run;