Coding Help needed - to create min and max dates with multiple by variables (some repeating)

[EIrvin]

Hello all:

I am stumped and need some help.  I have a data set where I am attempting to get min/max dates for by employee_id and role_cd.  I have used min/max and lags to try create an eff from/to date, but the problem lies when someone switches from one role to a new role, then back to the old.  I think the ideal situation would be to flag each change in role and then take the min/max of each role's time period, then create the lag date where there may be jumps in dates.

my super simplified dummy data set (attached) is on tab 1 with the results on the second tab.  The first result set showing what I get today - probably an order of operations thing - and the second is what I am looking for, but I can't for the life of me get this to work.

Any help will be greatly appreciated

El

[ballardw · Accepted solution]

Proc summary data=have nway;
   by notsorted employee_id role_cd;
   var nameofdatevariable;
   output out=want (drop=_type_) min=datemin max=datemax;
run;

Should get the max and min for each role as it changes.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[EIrvin]

Works beautifully - I don't think I've ever used Proc summary - and am not very data step savvy, except what I parse together from Ask Google 🙂 ! So thank you.

Also thank you for the note and instructions on the Excel and transforming that into code to post! Will certainly keep that in mind!