Class use in proc means causing data duplication

[Rjk]

Hi I'm facing a problem where using the class in the proc means step is causing duplication of data, one set with blank values in the class variable.

[Reeza · Accepted solution]

When you use a CLASS statement it does all levels, total and the for each level of the class variable. 

If you had two variables it would do the overall, all levels of the first variable, all levels of the second variable and all levels of both combined. The NWAY option will restrict the output to the 'highest' level and not have the lower levels. The _TYPE_ variable allows you to filter this out as well.

Check the results from the following:

proc means data=sashelp.class stackods;
class sex age;
var height;
ods output summary = want1;
run;

proc means data=sashelp.class stackods NWAY;
class sex age;
var height;
ods output summary=want2;
run;

EDIT: Apparently the STACKODS and ODS is formatted differently.

Here's a better example - check the 

proc means data=sashelp.class noprint ;
class sex age;
var height;
output out=want1 mean= sum= /autoname;
run;

proc means data=sashelp.class nway noprint ;
class sex age;
var height;
output out=want2 mean= sum= /autoname;
run;


Title 'example of no NWAY option';
proc print data=want1;
run;

Title 'example of NWAY option';
proc print data=want2;
run;

---

@Rjk wrote:
Hi I'm facing a problem where using the class in the proc means step is causing duplication of data, one set with blank values in the class variable.

---

[Rjk]

Thanks for the NWAY, thats what I was missing, makes more sense now 🙂

[Rick_SAS]

Please show the SAS syntax you are running and the results.

[Rjk]

HI, 

PLease refer below for sample data set, code and outputs.

/* sample data set */
data MyData;
input VarA$ 1 VarB$ 3 Bal;

datalines;
A X 100
A X 110
A X 120
A Y 130
B X 140
B Y 150
B Y 160
B Z 170
C Y 180
C Z 190
C Z 200
;

Run;

proc means data=work.MyData sum nonobs;
/* by ;*/
class VarA VarB;
var Bal;
output out=work.RST1 sum=SumBal;
run;

proc means data=work.MyData sum nonobs;
by VarA VarB;
/* class ;*/
var Bal;
output out=work.RST3 sum=SumBal;
run;

RESULTS :::::::  RST1

VarA	VarB	_TYPE_	_FREQ_	SumBal
		0	11	1650
	X	1	4	470
	Y	1	4	620
	Z	1	3	560
A		2	4	460
B		2	4	620
C		2	3	570
A	X	3	3	330
A	Y	3	1	130
B	X	3	1	140
B	Y	3	2	310
B	Z	3	1	170
C	Y	3	1	180
C	Z	3	2	390

Results RST3   :::::::::::::::::::::::::::::

VarA	VarB	_TYPE_	_FREQ_	SumBal
A	X	0	3	330
A	Y	0	1	130
B	X	0	1	140
B	Y	0	2	310
B	Z	0	1	170
C	Y	0	1	180
C	Z	0	2	390

RST 3 is the required results, but just wondering why RST1 had the extra data in there duplicated.

Looks like @Reeza and @Astounding  have answered my question 🙂

[ballardw]

If ALL of your class variables are "blank" then I would bet a small stack of $$ that the _type_=0. That is the overall summary for the entire data set. The same as you would get without any class variables.

[Rjk]

Thanks, I was wondering what _TYPE_ was for, makes sense now 🙂

[Astounding]

While you already have the right answer, you've received so much information that it may be difficult to sift out what you need here.  Within @Reeza 's post is mention of the NWAY option.  You should be adding that on your PROC MEANS statement:

proc means data=have nway;

That will remove the extra levels of summarization.

Sometimes those extra levels are helpful, but that's another story for another day. 

[Rjk]

Thanks, Thats what I was missing, makes a lot more sense now 🙂