need help on proc summary procedure

[joherndon]

raw data I have is 

Date	name	marks1	marks2	marks3
Aug-20	AAAA	30	60	80
Sep-20	BBBB	31	61	81
Oct-20	AAAA	32	62	82
Aug-20	BBBB	33	63	83
Sep-20	AAAA	34	64	84
Oct-20	BBBB	35	65	85
Aug-20	AAAA	36	66	86
Sep-20	BBBB	37	67	87
Oct-20	AAAA	38	68	88
Aug-20	BBBB	39	69	89
Sep-20	AAAA	40	70	90
Oct-20	BBBB	41	71	91

                        I want to transform my raw data to be like this(below)

Aug-20	AAAA	66	126	166
Aug-20	BBBB	72	132	172
Sep-20	AAAA	74	134	174
Sep-20	BBBB	68	128	168
Oct-20	AAAA	70	130	170
Oct-20	BBBB	76	136	176

                                             Which procedure can i use to do get the desired output. Please give me the  syntax. I need answer as soon as possible.

[Kurt_Bremser]

Use

class date name;

and

var marks:;

in the summary procedure.

To create a dataset, use the output statement.

For detailed help, show the code you tried, and the log from it.

[joherndon]

Thanks for your quick reply. I used the same syntax you have provided. I am getting following out put. I don't want the first six rows.

I can use missing function and delete those values. but In my work I have millions of rows (four category variables). I used drop= _freq_ _type_  for my out put data set. So is there any short cut to get the data I want

generated out put

.	.	426	786	1026
.	AAAA	210	390	510
.	BBBB	216	396	516
Aug-20	.	138	258	338
Sep-20	.	142	262	342
Oct-20	.	146	266	346
Aug-20	AAAA	66	126	166
Aug-20	BBBB	72	132	172
Sep-20	AAAA	74	134	174
Sep-20	BBBB	68	128	168
Oct-20	AAAA	70	130	170
Oct-20	BBBB	76	136	176

desired output

Aug-20	AAAA	66	126	166
Aug-20	BBBB	72	132	172
Sep-20	AAAA	74	134	174
Sep-20	BBBB	68	128	168
Oct-20	AAAA	70	130	170
Oct-20	BBBB	76	136	176

my syntax:

proc summary data=have;

class date name;

var marks:;

output out=want (drop=_freq_ _type_) sum=;

run;

Thanks in advance;

[ed_sas_member]

Hi @joherndon 

You just need to add a WAYS statement as follows, which specifies the number of ways to make unique combinations of class variables:

proc summary data=have;
	class date name;
	var marks:;
	ways 2;
	output out=want (drop=_freq_ _type_) sum=;
run;

Best,

[PaigeMiller]

proc summary data=have nway;

class date name;

var marks:;

output out=want (drop=_freq_ _type_) sum=;

run;

[Tom · Accepted solution]

Why did you drop the _TYPE_ variable?  That is the one that allows you to know which observations represent the overall summaries(_TYPE_=0) and which are the one way summaries (_TYPE_ in (1,2)) and which are the two way summaries.

If you only want the most detailed combinations then add the NWAY option to the PROC statement.

[joherndon]

Thanks for your help