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 |
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.