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         ... View more

Thats not a join then, its a transpose.  Post test data in the form of a datastep to get working code (its probably been mentioned several times before), as such this code is just an un-tested guess: proc transpose data=datab out=datab_t; by refer_id; var role; id role; run; proc sort data=dataa nodupkey; by refer_id person; run; data want; merge dataa datab; by refer_id; run; Not sure what value there is in creating a whole set of cells with the same data though, the table c just seems to bloat the data to my mind. ... View more

I believe that Patrick is on the right path to helping you.  If you were able to run a profile on your data, the report results would give you the information you are asking.   Running a profile on data is a common first step in understanding your data and monintoring the data's quality. ... View more

Anna,   when I wrote this: Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create a data step that lets us recreate your dataset with all attributes and values I really meant it. Posting a picture that does not even show your complete data is a) not very helpful and b) at best impolite, forcing us to type the data from screen.   THIS is how you post example data: data age_test; input id lst_upd_dt :datetime19. brth_dt :datetime19.; format lst_upd_dt brth_dt datetime19.; cards; 150002 01jan2001:12:07:00 06feb1956:00:00:00 ; run; Now, I applied several different types of logic to that, that give slightly different results: data ageatreferral; set age_test; l_up_dt = datepart(lst_upd_dt); dob = datepart(brth_dt); age_at_referral = yrdif(dob,l_up_dt); age_at_referral2 = intck('year',dob,l_up_dt); time_lived = lst_upd_dt - brth_dt; format time_lived time20.; run; One of those (time_lived) is the application of my answer to "What is if I wanted to know the exact time from b-a?" ... View more