topic Re: Total by group in SAS Programming

Total by group

monday89 — Mon, 16 Mar 2020 14:17:27 GMT

I have the following dataset. I would like to create a new dataset that totals per column and MIN and Max for the date columns

Have:

Name	Screened	Eligible	Enrolled	Rate	FirstDate	LastDate
Amy	9	3	3	33%	12/2/2019	1/2/2020
Bianca	12	5	4	33%	12/1/2019	2/20/2020
Casey	9	4	4	44%	8/12/2019	1/29/2020
Deborah	10	9	8	80%	9/21/2019	3/1/2020
Eileen	1	1	1	100%	8/21/2019	8/21/2019
Fiona	12	11	2	17%	11/18/2019	2/9/2020
Georgia	19	12	11	58%	9/18/2019	12/31/2019
Harriet	32	31	23	72%	12/31/2019	2/20/2020
India	12	10	7	58%	4/30/2019	2/15/2020
Jennifer	11	8	5	45%	7/23/2019	3/1/2020

Want (rate = enrolled/screened)

Name	Screened	Eligible	Enrolled	Rate	FirstDate	LastDate
Amy	9	3	3	33%	12/2/2019	1/2/2020
Bianca	12	5	4	33%	12/1/2019	2/20/2020
Casey	9	4	4	44%	8/12/2019	1/29/2020
Deborah	10	9	8	80%	9/21/2019	3/1/2020
Eileen	1	1	1	100%	8/21/2019	8/21/2019
Fiona	12	11	2	17%	11/18/2019	2/9/2020
Georgia	19	12	11	58%	9/18/2019	12/31/2019
Harriet	32	31	23	72%	12/31/2019	2/20/2020
India	12	10	7	58%	4/30/2019	2/15/2020
Jennifer	11	8	5	45%	7/23/2019	3/1/2020
Total	127	94	68	54%	4/30/2019	3/1/2020

I tried proc means and sql commands but i am really stuck

Re: Total by group

PeterClemmensen — Mon, 16 Mar 2020 14:25:31 GMT

Is this for reporting purposes or do you want a SAS data set?

Re: Total by group

monday89 — Mon, 16 Mar 2020 14:37:55 GMT

I need an actual dataset. Thanks! I really appreciate it

Re: Total by group

ed_sas_member — Mon, 16 Mar 2020 15:56:30 GMT

Hi @monday89

Here is one approach to achieve this, using PROC SQL:

data have;
	infile datalines dlm="09"x;
	input Name:$20. Screened Eligible Enrolled Rate:percent8.0 FirstDate:MMDDYY10. LastDate:MMDDYY10.;
	format FirstDate LastDate MMDDYY10.;
	datalines;
Amy	9	3	3	33%	12/2/2019	1/2/2020
Bianca	12	5	4	33%	12/1/2019	2/20/2020
Casey	9	4	4	44%	8/12/2019	1/29/2020
Deborah	10	9	8	80%	9/21/2019	3/1/2020
Eileen	1	1	1	100%	8/21/2019	8/21/2019
Fiona	12	11	2	17%	11/18/2019	2/9/2020
Georgia	19	12	11	58%	9/18/2019	12/31/2019
Harriet	32	31	23	72%	12/31/2019	2/20/2020
India	12	10	7	58%	4/30/2019	2/15/2020
Jennifer	11	8	5	45%	7/23/2019	3/1/2020
;
run;

proc sql;
	create table want1 as
	
	(select* from have
	union corr all
	select "Total" as Name,
			sum(Screened) as Screened,
			sum(Eligible) as Eligible,
			sum(Enrolled) as Enrolled,
			calculated enrolled/ calculated screened as rate,
			min(FirstDate) as FirstDate,
			max(LastDate) as LastDate
	from have);
quit;

data want;
	set want1;
	format rate percent8.0;
run;

My best,

Re: Total by group

monday89 — Tue, 17 Mar 2020 14:11:43 GMT

Thanks so much ! It works