topic Re: Select rows when condition is true in SAS Procedures

Select rows when condition is true

DathanMD — Wed, 05 Feb 2020 21:46:31 GMT

Hello Colleagues, I am new to SAS and I would like help with some data manipulation from someone here.

Question: I have data as shown below,I would like to select rows where a given id has “manager” appearing for the first time in the job category (for that id).

Data I have:

id	Start_date	End-date	JOB
1	12JUN2015	14JUN2016	CHEF
1	14JUN2016	15JUN2016	MANAGER
1	15JUN2016	17JAN2018	DRIVER
1	18JAN2015	19JAN2018	ADMIN
1	19JAN2018	19OCT2019	MANAGER
2	12JUN2015	14JUN2016	CHEF
2	14JUN2016	15JUN2016	MANAGER
2	15JUN2016	17JAN2018	MANAGER
3	18JAN2015	19JAN2018	ADMIN
3	19JAN2018	19OCT2019	MANAGER
4	12JUN2015	14JUN2016	CHEF

Data that I want

id	Start_date	End-date	JOB
1	14JUN2016	15JUN2016	MANAGER
2	14JUN2016	15JUN2016	MANAGER
3	19JAN2018	19OCT2019	MANAGER

Thanks

Dathan Byonanebye

Re: Select rows when condition is true

ed_sas_member — Wed, 05 Feb 2020 21:59:06 GMT

Hi @DathanMD

Here is a way to achieve this :

proc sort data=have out=have_sorted;
by id job start_date;
run;
 
data want;
set have_sorted;
by id job start_date;
if first.job and job=‘MANAGER’ then output;
run;

Re: Select rows when condition is true

novinosrin — Wed, 05 Feb 2020 22:20:48 GMT

Hi @DathanMD


data have;
input id (Start_date End_date)(:date9.) JOB $;
format Start_date End_date date9.;
cards;
1
12JUN2015
14JUN2016
CHEF
1
14JUN2016
15JUN2016
MANAGER
1
15JUN2016
17JAN2018
DRIVER
1
18JAN2015
19JAN2018
ADMIN
1
19JAN2018
19OCT2019
MANAGER
2
12JUN2015
14JUN2016
CHEF
2
14JUN2016
15JUN2016
MANAGER
2
15JUN2016
17JAN2018
MANAGER
3
18JAN2015
19JAN2018
ADMIN
3
19JAN2018
19OCT2019
MANAGER
4
12JUN2015
14JUN2016
CHEF
;

proc sql;
create table want(drop=t) as 
select *, job='MANAGER' as t
from have
group by id,t
having t and min(start_date)=start_date;
quit;

/*or just*/

proc sql;
create table want as 
select *
from have
where job='MANAGER'
group by id
having min(start_date)=start_date;
quit;

Re: Select rows when condition is true

Kurt_Bremser — Wed, 05 Feb 2020 22:13:45 GMT

I think @DathanMD rather needs this:

data want;
set have_sorted (where=(job = 'MANAGER'));
by id job start_date;
if first.job;
run;