topic Re: Indicate only specific incidences through a set of repeated measurements with different roles in SAS Programming

Indicate only specific incidences through a set of repeated measurements with different roles

AnnaNZ — Sun, 09 Jul 2017 20:56:58 GMT

Another question relating to my last post.

I have a large dataset (5mio) where I need to code the times a client has been moved. Time of that move is given by the beginning and the end time (datetime)

BEG_RMVL_DT

END_RMVL_DT

and the move incident is given by

RMVL_ID

Whilst the dataset has people of many roles in ROLE_IN_REFERRAL I am only interested how often an 'Alleged Victim' has been moved.

Ideally, when an 'Alleged Victim' has been moved, I would want to show up a 1 in RMVL.

The code below does this in part. But I am not sure (and I tried a lot) how to get around the fact that whenever there is a different role, where like in :

If ROLE_IN_REFERRAL in ('AP','LC', ) then RMVL = .; SAS starts to count from 1 again.

How can I tell SAS to count through episodes of other ROLE_IN_REFERRAL ?

ROLE_IN_REFERR

So if you look in the dataset below, then you see, the first time for 'Alleged Victim' it is right and I have the desired result, I could filter RMVL for 1 in a further step.

But a few lines further down after LC and AP, SAS starts to count from 1 again, although I am still having the same RMVL_ID: 1557066

So I would want SAS to indicate the first time 'Alleged Victim' appears in RMVL_ID= 1557066, then in

1558931

1568222

1568226

It sort of does it but starts counting with 1 after every ROLE_IN_REFERRAL that is not 'Alleged Victim'

How do I get aroud that? I tried to formulate a code in a second step, but I still would have to say something like

IF ROLE_IN_REFERRAL in ('Alleged Victim') and RMVL has already been 1 once, then do But how would you do that?

Is tgere a more elegant solution?

Many thanks

  Proc sort data=   Kea.REFER_DT1 by  RMVL_ID ; 
  Data Kea.REFER_DT2; 
Set  Kea.REFER_DT1;     
RMVL + 1;
by  RMVL_ID ; 
IF ROLE_IN_REFERRAL in ('Alleged Victim') and first.RMVL_ID  then  RMVL = 1 ; else 
If ROLE_IN_REFERRAL in ('AP','LC',  )  then RMVL = .; 
run;

REFER_ID	ROLE_IN_REFERRAL	RMVL_ID	BEG_RMVL_DT	END_RMVL_DT	RMVL
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	1
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	2
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	1
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	2
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	3
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	4
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800113	Alleged Victim	1558931	24Aug2006 0:00:00	03Oct2006 0:00:00	1
1800113	Alleged Victim	1568222	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	4
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	4
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	5
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	6
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	7
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	8
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	4
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	5
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	6
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	7
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	8
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	9
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	10
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	11
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	12
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	13
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	14
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	15
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	16
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	17
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	18
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	19
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	AP	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	1
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	2
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	3
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	4
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	5
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	6
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	7
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	8
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	9
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	10
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	11
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	12
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	13
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	14
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	15
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	16
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	17
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	18
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	19
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	20
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	21
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	22
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	23
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	24
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	25
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	26
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	27
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	28
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	29
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	30
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	31
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	32
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	33
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	34
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	35
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	36
1800113	Alleged Victim	1568226	07Jan2008 0:00:00	20Nov2009 0:00:00	37

Re: Indicate only specific incidences through a set of repeated measurements with different roles

AnnaNZ — Sun, 09 Jul 2017 21:53:06 GMT

Maybe it was easier to count how often RMVL_ID changes for 'Alleged Victim' and

therfore to create a variable "count" that states how many times 'Alleged Victim' was moved?

How could I get that?

Re: Indicate only specific incidences through a set of repeated measurements with different roles

AnnaNZ — Sun, 09 Jul 2017 23:53:23 GMT

what could I google for, it is like counting how often a variable changes grouped by one variable and set for specific conditions / roles. I have not found anything yet. Has anybody an idea ?

Re: Indicate only specific incidences through a set of repeated measurements with different roles

Reeza — Mon, 10 Jul 2017 00:19:10 GMT

Having a hard time working through your question - a lot in there.

Can you simplify it to what you have and what you want, with just one or two cases?

Then it will be easier for us to test solutions. Also, if you can, please try and post your sample data as a data step since that will help speed up the process. Instructions on how to do so can be found here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Indicate only specific incidences through a set of repeated measurements with different roles

AnnaNZ — Mon, 10 Jul 2017 01:18:56 GMT

Many thanks for looking into this.

No sucess with creating the macro with SAS 9.4 so far.

I should only need the input data set name, and that is not working.

%data2datastep(input data set name);

But what I want in simple terms is:

Count how often 'Alleged Victim' (or any other ROLE_IN_REFERRAL ) defined / grouped by REFER_ID was moved.

Below is what I have so far. But 'Alleged Victim' with REFER_ID : 1800114 and RMVL_ID:1557066 should only come up once with 1 in RMVL, nut with my current code it does so every time the ROLE_IN_REFERRAL changes, like after LC and AP - at the moment every time that 'Alleged Victim' appears again, it also starts counting again. While this is a way that would need further work, as I would need to summarize the finding, tere may alkso be a solution to simply count changes grouped by REFER_ID and RMVL_ID

Does this make more sense?

REFER_ID	ROLE_IN_REFERRAL	RMVL_ID	BEG_RMVL_DT	END_RMVL_DT	RMVL
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	1
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	2
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	AP	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	1
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	2
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	3
1800114	Alleged Victim	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00	4
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00
1800114	LC	1557066	10Dec2006 0:00:00	11Sep2007 0:00:00