BookmarkSubscribeRSS Feed
AnnaNZ
Quartz | Level 8

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_DTEND_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_IDROLE_IN_REFERRALRMVL_IDBEG_RMVL_DTEND_RMVL_DTRMVL
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:001
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:002
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:001
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:002
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:003
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:004
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800113Alleged Victim155893124Aug2006 0:00:0003Oct2006 0:00:001
1800113Alleged Victim156822207Jan2008 0:00:0020Nov2009 0:00:001
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:004
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:004
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:005
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:006
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:007
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:008
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:004
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:005
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:006
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:007
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:008
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:009
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0010
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0011
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0012
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0013
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0014
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0015
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0016
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0017
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0018
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0019
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113AP156822607Jan2008 0:00:0020Nov2009 0:00:00 
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:001
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:002
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:003
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:004
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:005
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:006
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:007
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:008
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:009
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0010
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0011
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0012
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0013
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0014
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0015
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0016
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0017
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0018
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0019
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0020
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0021
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0022
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0023
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0024
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0025
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0026
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0027
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0028
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0029
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0030
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0031
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0032
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0033
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0034
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0035
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0036
1800113Alleged Victim156822607Jan2008 0:00:0020Nov2009 0:00:0037

 

4 REPLIES 4
AnnaNZ
Quartz | Level 8

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?

AnnaNZ
Quartz | Level 8

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 ?

Reeza
Super User

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-dat...

 

 

AnnaNZ
Quartz | Level 8

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_IDROLE_IN_REFERRALRMVL_IDBEG_RMVL_DTEND_RMVL_DTRMVL
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:001
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:002
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114AP155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:001
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:002
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:003
1800114Alleged Victim155706610Dec2006 0:00:0011Sep2007 0:00:004
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 
1800114LC155706610Dec2006 0:00:0011Sep2007 0:00:00 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 778 views
  • 0 likes
  • 2 in conversation