Multiple if condition on status

[novinosrin]

@bknitch  Alternatively, try the datastep below. I wanted to avoid this on purpose for the reason I find this increasingly boring, predictable and obvious, but if this is what that would work, why not?

proc sql; 
create table TEST_STATUS (MEMB_ID num, PAD_FLAG char(1),  DECILE num, DOS_YEAR num, PROGRESSION char(6),  STATUS char(15));
insert into TEST_STATUS 
values(1231,	'N', 	8, 2019,'STAYER','	DROPPED')
values(1231,    'N', 	8, 2020,'STAYER','	DROPPED')
values(1241,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1241,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1251,	'N', 	9, 2019,'STAYER','	FOUND  ')
values(1251,	'N', 	9, 2020,'STAYER','	FOUND  ')
values(1261,	'N', 	2, 2019,'STAYER','	DROPPED')
values(1271,	'N', 	7, 2019,'STAYER','	DROPPED')
values(1271,	'N', 	7, 2020,'STAYER','	DROPPED')
values(1281,	'N', 	9, 2019,'STAYER','	DROPPED')
values(1281,	'N', 	9, 2020,'STAYER','	DROPPED')
values(1291,	'Y', 	1, 2019,'STAYER','	FOUND  ')
values(1291,	'Y', 	1, 2020,'STAYER','	FOUND  ')
values(1211,    'Y', 	8, 2019,'STAYER','	NEW    ')
values(1211,	'Y', 	8, 2020,'STAYER','	DROPPED')
values(1201,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1201,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1301,	'Y', 	1, 2019,'STAYER','	FOUND  ')
values(1301,	'Y', 	1, 2020,'STAYER','	DROPPED')
values(1311,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1311,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1331,	'N', 	1, 2019,'STAYER','	DROPPED')
values(1331,    'N', 	1, 2020,'STAYER','	DROPPED')
;
quit;

data want;
 do _iorc_=1 by 1 until(last.memb_id);
  set test_status;
  by memb_id notsorted;
  if strip(status)='DROPPED' then _n_=0;
 end;
 do _iorc_=1 to _iorc_;
  set test_status;
  Target=not _n_;
  output;
 end;
run;

[Reeza]

Do you always have two years of data present, 2020 and 2019 or can you miss a year or have more years?

SAS only operates on a single row at a time, so you need to use LAG to get the previous value and do your comparison using the lagged variable. 

---

@bknitch wrote:

proc sql; 
create table TEST_STATUS (MEMB_ID num, PAD_FLAG char(1),  DECILE num, DOS_YEAR num, PROGRESSION char(6),  STATUS char(8));
insert into TEST_STATUS 
values(1231,	'N', 	8, 2019,'STAYER','	DROPPED')
values(1231,    'N', 	8, 2020,'STAYER','	DROPPED')
values(1241,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1241,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1251,	'N', 	9, 2019,'STAYER','	FOUND  ')
values(1251,	'N', 	9, 2020,'STAYER','	FOUND  ')
values(1261,	'N', 	2, 2019,'STAYER','	DROPPED')
values(1271,	'N', 	7, 2019,'STAYER','	DROPPED')
values(1271,	'N', 	7, 2020,'STAYER','	DROPPED')
values(1281,	'N', 	9, 2019,'STAYER','	DROPPED')
values(1281,	'N', 	9, 2020,'STAYER','	DROPPED')
values(1291,	'Y', 	1, 2019,'STAYER','	FOUND  ')
values(1291,	'Y', 	1, 2020,'STAYER','	FOUND  ')
values(1211,    'Y', 	8, 2019,'STAYER','	NEW    ')
values(1211,	'Y', 	8, 2020,'STAYER','	DROPPED')
values(1201,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1201,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1301,	'Y', 	1, 2019,'STAYER','	FOUND  ')
values(1301,	'Y', 	1, 2020,'STAYER','	DROPPED')
values(1311,	'Y', 	9, 2019,'STAYER','	FOUND  ')
values(1311,	'Y', 	9, 2020,'STAYER','	DROPPED')
values(1331,	'N', 	1, 2019,'STAYER','	DROPPED')
values(1331,    'N', 	1, 2020,'STAYER','	DROPPED')
;
run;

/* EXAMPLE OF LOGIC I NEED, this code does not work */
data WANT;
set TEST_STATUS;
if DOS_YEAR = 2019 and STATUS in ('NEW','FOUND') and DOS_YEAR = 2020 and STATUS in ('DROPPED') then TARGET=1; ELSE TARGET=0;
if DOS_YEAR = 2019 and STATUS = 'DROPPED' and DOS_YEAR = 2020 and STATUS = 'DROPPED' then TARGET=1; ELSE TARGET=0;
run;

I'm trying to find a way to weed out non targeted membership. I need to exclude members with a status of 'NEW' or 'FOUND' in 2020. I'm also trying to target members where 'DROPPED' status or 'NEW','FOUND' status was in 2019 and contained a 'DROPPED' status in 2020. When i add multiple conditions it does not work. Any help would be appreciated. It's also importan to note there are scenarios where a member only shows up for 1 year, in this case I need to target where if member status in 2019 = 'DROPPED'  I need to target. 

These are the results i'm looking for 

MEMB_ID	PAD_FLAG	DECILE	DOS_YEAR	PROGRESSION	STATUS	TARGET
1231	N	8	2019	STAYER	DROPPED	1
1231	N	8	2020	STAYER	DROPPED	1
1241	Y	9	2019	STAYER	FOUND	1
1241	Y	9	2020	STAYER	DROPPED	1
1251	N	9	2019	STAYER	FOUND	0
1251	N	9	2020	STAYER	FOUND	0
1261	N	2	2019	STAYER	DROPPED	1
1271	N	7	2019	STAYER	DROPPED	1
1271	N	7	2020	STAYER	DROPPED	1
1281	N	9	2019	STAYER	DROPPED	1
1281	N	9	2020	STAYER	DROPPED	1
1291	Y	1	2019	STAYER	FOUND	0
1291	Y	1	2020	STAYER	FOUND	0
1211	Y	8	2019	STAYER	NEW	1
1211	Y	8	2020	STAYER	DROPPED	1
1201	Y	9	2019	STAYER	FOUND	1
1201	Y	9	2020	STAYER	DROPPED	1
1301	Y	1	2019	STAYER	FOUND	1
1301	Y	1	2020	STAYER	DROPPED	1
1311	Y	9	2019	STAYER	FOUND	1
1311	Y	9	2020	STAYER	DROPPED	1
1331	N	1	2019	STAYER	DROPPED	1
1331	N	1	2020	STAYER	DROPPED	1

---

[bknitch]

Hi @Reeza 

there are scnearios where i may have just 1 year so either 2019 or 2020. In those cases if any of those are = 'DROPPED' i need to target those as well. I've used Lag function between long date periods to show gaps and counts between since last captured or found etc. Not sure how the LAG would play a part in the statues.