Hi
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 |
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?
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 ?
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:
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 |
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.