topic Re: List the smallest date and largest date? in SAS Programming

List the smallest date and largest date?

ybz12003 — Thu, 29 Jul 2021 21:08:15 GMT

Hello,

I have dataset Test list below. I would like to get the smallest date and the largest date from the each site. For example, the result for Site 1 should be: Starting Date 2016-12-01; Ending Date 2020--5-29. Please let me know how to approach it, thank you.

data test;

format Sites 1. ScreenDate YYMMDD10.;

infile datalines dsd;

input Sites ScreenDate : yymmdd10.;

datalines;

1, 2016-12-01,

1, 2016-12-21,

1, 2018-08-15,

1, 2020-05-29,

2, 2017-12-03,

2, 2015-10-23,

2, 2016-09-14,

3, 2019-12-10,

3, 2004-12-09,

3, 2006-08-20,

3, 2016-12-10,

4, 2000-05-06,

4, 2007-12-06,

4, 2016-09-27,

;

Proc sort data=test; by sites ScreenDate; run;

Re: List the smallest date and largest date?

jimbarbour — Thu, 29 Jul 2021 21:19:09 GMT

Have you tried Proc SQL with a GROUP BY clause?

See example code followed by the results, below.

Jim

data test;

format Sites 1. ScreenDate YYMMDD10.;

infile datalines dsd;

input Sites ScreenDate : yymmdd10.;

datalines;
1, 2016-12-01,
1, 2016-12-21,
1, 2018-08-15,
1, 2020-05-29,
2, 2017-12-03,
2, 2015-10-23,
2, 2016-09-14,
3, 2019-12-10,
3, 2004-12-09,
3, 2006-08-20,
3, 2016-12-10,
4, 2000-05-06,
4, 2007-12-06,
4, 2016-09-27,
;

Proc sort data=test; by sites ScreenDate; run;


PROC	SQL;
	SELECT Sites 
		,MIN(ScreenDate)	AS	Min_Screen_Date	FORMAT=YYMMDDD10.
		,MAX(ScreenDate)	AS	Max_Screen_Date	FORMAT=YYMMDDD10.
		FROM	WORK.TEST
		GROUP	BY	Sites
		;
QUIT;

Re: List the smallest date and largest date?

ballardw — Thu, 29 Jul 2021 21:19:09 GMT

Do you need a data set or report for people to read?

Data set:

Proc summary data=test nway;
   class site;
   var screendate;
   output out=want(drop=_type_ _freq_) min= max= /autoname;
run;

The autoname creates variables screendate_min and screendate_max. You would want to attach a format to the variables to be read by people easily. Or you could use min=StartDate max=EndDate to set those names for the result.

Re: List the smallest date and largest date?

maguiremq — Thu, 29 Jul 2021 21:25:56 GMT

There are quite a few ways. Here's how I'd tackle it in either DATA step form or PROC SQL:

proc means 
	data = test noprint;
		class sites;
		var screendate;
		ways 1;
			output out = want (drop = _:)
				min =
				max = / autoname;
run;

Obs 	Sites 	ScreenDate_Min 	ScreenDate_Max
1 	1 	2016-12-01 	2020-05-29
2 	2 	2015-10-23 	2017-12-03
3 	3 	2004-12-09 	2019-12-10
4 	4 	2000-05-06 	2016-09-27

PROC SQL:

proc sql;
	create table 	want_sql as
		select
					sites,
					min(screendate) as min_screendate format = yymmdd10.,
					max(screendate) as max_screendate format = yymmdd10.
		from
					test
		group by
					sites;
quit;

Obs 	Sites 	min_screendate 	max_screendate
1 	1 	2016-12-01 	2020-05-29
2 	2 	2015-10-23 	2017-12-03
3 	3 	2004-12-09 	2019-12-10
4 	4 	2000-05-06 	2016-09-27

Re: List the smallest date and largest date?

ybz12003 — Thu, 29 Jul 2021 21:29:07 GMT

Thank you so much for the prompt reply. I got it work!