Following table:
name | status | version |
ABCD | not recruiting | 4 |
ABCD | recruiting | 4 |
EFGH | not recruiting | 4 |
HIJK | recruiting | 4 |
LMNO | not recruiting | 4 |
LMNO | recruiting | 4 |
QRST | not recruiting | 4 |
Want to only export second entry of the name
name | status | version |
ABCD | recruiting | 4 |
EFGH | not recruiting | 4 |
HIJK | recruiting | 4 |
LMNO | recruiting | 4 |
QRST | not recruiting | 4 |
I tried using row number but i am losing version
data have;
input name $ status & $30. version;
cards;
ABCD not recruiting 4
ABCD recruiting 4
EFGH not recruiting 4
HIJK recruiting 4
LMNO not recruiting 4
LMNO recruiting 4
QRST not recruiting 4
;
proc sql;
create table want(drop=m) as
select *,monotonic() as m
from have
group by name
having max(m)=m
order by m;
quit;
Use FIRST.
This handles the cases of a single record (by name) and two records but will not handle a case where you have three records for name.
If that's the case you'll need to add in a counter variable and expand the IF condition.
data want;
set have;
by name;
if not first.name or (first.name and last.name);
run;
EDIT: It seems like you just want the recruiting records. Any reason to not use that as the filter rule?
where status='recruiting';
Slight variation to not have assumption of sets on 2
data have;
input name $ status & $30. version;
cards;
ABCD not recruiting 4
ABCD recruiting 4
EFGH not recruiting 4
HIJK recruiting 4
LMNO not recruiting 4
LMNO recruiting 4
QRST not recruiting 4
;
proc sql;
create table want(drop=m) as
select *,monotonic() as m
from have
group by name
having min(m)+1=m or count(*)=1
order by m;
quit;
And the obvious, but lately I am becoming bored of obvious ones though
data have;
input name $ status & $30. version;
cards;
ABCD not recruiting 4
ABCD recruiting 4
EFGH not recruiting 4
HIJK recruiting 4
LMNO not recruiting 4
LMNO recruiting 4
QRST not recruiting 4
;
data want;
do _n_=1 by 1 until(last.name);
set have;
by name;
if _n_=2 then output;
end;
if _n_=1 then output;
run;
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.