topic Re: second entry extraction in SAS Programming

second entry extraction

radhikaa4 — Fri, 13 Dec 2019 16:10:44 GMT

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

Re: second entry extraction

novinosrin — Fri, 13 Dec 2019 16:18:14 GMT


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;

Re: second entry extraction

Reeza — Fri, 13 Dec 2019 16:23:47 GMT

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';

Re: second entry extraction

novinosrin — Fri, 13 Dec 2019 16:31:24 GMT

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;

Re: second entry extraction

novinosrin — Fri, 13 Dec 2019 16:38:44 GMT

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;