BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dave25
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dave25
Quartz | Level 8

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;

 

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Quartz | Level 8

Apologies - i was hoping to get this result:

 

 

 

A2

Grade

A1

B1

C1

A2a

A2b

1

1

1

1

1

1

Dave25
Quartz | Level 8

 

"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
Super User

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
Quartz | Level 8

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;

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4694 views
  • 0 likes
  • 2 in conversation