topic Re: need help on proc summary procedure in SAS Procedures

need help on proc summary procedure

joherndon — Sat, 22 Feb 2020 03:00:55 GMT

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.

Re: need help on proc summary procedure

Kurt_Bremser — Sat, 22 Feb 2020 08:31:18 GMT

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.

Re: need help on proc summary procedure

joherndon — Sat, 22 Feb 2020 14:13:09 GMT

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;

Re: need help on proc summary procedure

ed_sas_member — Sat, 22 Feb 2020 14:39:59 GMT

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,

Re: need help on proc summary procedure

PaigeMiller — Sat, 22 Feb 2020 14:52:43 GMT

proc summary data=have nway;

class date name;

var marks:;

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

run;

Re: need help on proc summary procedure

Tom — Sat, 22 Feb 2020 15:00:39 GMT

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.

Re: need help on proc summary procedure

joherndon — Tue, 25 Feb 2020 19:53:51 GMT

Thanks for your help