topic Re: Limit observation count by variable in SAS Programming

Limit observation count by variable

prad001 — Fri, 10 Feb 2023 16:24:17 GMT

I want to retrieve all the LATEST and MAXIMUM of 2(two) records by state.

Example: For PA state, I want to retrieve 1 and 3 LicenceStateCd.. etc..

LicenceStateCd	State	Date
1	PA	1/10/2022
2	PA	1/10/2010
3	PA	1/10/2020
4	CA	1/10/2000
5	CA	1/10/2000
6	CA	1/10/2010
7	MI	1/10/2022
8	VA	1/10/2022
9	MD	1/20/2022
10	VA	1/10/2022

Re: Limit observation count by variable

PaigeMiller — Fri, 10 Feb 2023 16:38:52 GMT

I'm not sure I understand this part well enough to program it.

retrieve all the LATEST and MAXIMUM of 2(two) records by state

Can you explain further?

Re: Limit observation count by variable

ballardw — Fri, 10 Feb 2023 18:10:00 GMT

I am assuming that you want the two latest dates, if present AND that your date is an actual SAS date value.

Note the first data step to make a working data set we can use.

data have;
   input LicenceStateCd 	State $	Date :mmddyy10.;
   format date mmddyy10.;
datalines;
1 	PA 	1/10/2022
2 	PA 	1/10/2010
3 	PA 	1/10/2020
4 	CA 	1/10/2000
5 	CA 	1/10/2000
6 	CA 	1/10/2010
7 	MI 	1/10/2022
8 	VA 	1/10/2022
9 	MD 	1/20/2022
10 	VA 	1/10/2022
;

Proc sort data=have;
   by state descending date;
run;

data want;
  set have;
  by state;
  retain counter;
  if first.state then counter=1;
  else counter+1;
  if counter le 2;
  drop counter;
run;

The proc sort will group the state by decreasing (new to oldest) date.

The By statement creates automatic variables that indicate whether a record is first or last in a group and can be used to do things conditionally at the group boundary, in this case reset a counter that counts the number of records in each State group.

The Retain means that the value of Counter is maintained across the data step boundary and accumulates a count.

The If Counter le 2 mean only keep the first two records.

Re: Limit observation count by variable

Patrick — Sun, 12 Feb 2023 02:07:06 GMT

Below SQL will per state return the row with the max date and the row with the max LicenceStateCd.

- If the two max are in the same row then only a single row will be returned.

- ALL rows with a max value will be returned (so can be one or several). You would need to further define what should happen in such a case if that's not what you want (like below for VA).

data have;
  input LicenceStateCd State $ Date :mmddyy10.;
  format date mmddyy10.;
  datalines;
1 PA 1/10/2022
2 PA 1/10/2010
3 PA 1/10/2020
4 CA 1/10/2000
5 CA 1/10/2000
6 CA 1/10/2010
7 MI 1/10/2022
8 VA 1/10/2022
9 MD 1/20/2022
10 VA 1/10/2022
;

proc sql;
  select *
  from have
  group by state
  having max(date)=date or max(LicenceStateCd)=LicenceStateCd
  order by state, date
  ;
quit;