Hi,
I want to find the headcount and ie Attrtion in perticular month
sending the table I have and the report output I'm expecting
have
ID | DateofJoin | DateOfRelieving | Gender | Location | Vertical |
2231 | 04/03/13 | 09/01/16 | F | NY | A |
5012 | 11/19/13 | M | NY | B | |
5407 | 12/01/14 | M | NY | B | |
5871 | 05/08/14 | 09/02/16 | F | NY | B |
6388 | 09/29/14 | F | NY | BT | |
6662 | 11/05/14 | 09/02/16 | M | NY | C |
6675 | 11/06/14 | M | NY | B | |
7436 | 03/04/15 | M | ND | B | |
7944 | 05/04/15 | M | ND | B | |
8467 | 06/29/15 | 09/02/16 | F | NY | B |
8888 | 07/27/15 | F | ND | B | |
9207 | 09/01/15 | M | NY | S | |
10599 | 02/15/16 | M | NY | BT | |
10995 | 03/31/16 | 09/02/16 | F | NY | B |
11957 | 07/04/16 | 09/02/16 | M | NY | C |
12657 | 08/29/16 | 09/02/16 | F | ND | C |
output I am expecting;
Month | Location | Vertical | Gender | Headcount | Attrition |
I am able to get the attrition for the combination (Month, Location Vertical, Gender ) but
we'll need do loops for headcount as employee joined in May13 and left in Aug15 will be available only for that month (May13- July13) and after it will be attrition (ie for Aug15)
Thanks in Advance
I'll attach sample input in xls format for reference.
Well, first off, its a good idea to post test data in the form of a datastep. I have typed in a couple of rows to get this code working, but it isn't tested really due to the lack of datastep test data. It creates a dateset based on lowest date and highest date, then merges the original data back to that per month so you end up with data per month which can be counted:
data have; id=2231; dateofjoin="03apr2013"d; dateofrelieving="09jan2016"d; gender="F"; location="NY"; vertical="A"; output; id=5012; dateofjoin="19nov2013"d; dateofrelieving=.; gender="M"; location="NY"; vertical="B"; output; run; proc sql noprint; select min(dateofjoin) into :low from have; select max(max(dateofjoin),max(dateofrelieving)) into :high from have; quit; data template; do i=0 to intck('month',&low.,&high.); dt=intnx('month',&low.,i,"BEGINNING"); output; end; format dt yymmdd10.; run; proc sql; create table WANT as select TEMP.DT, HAVE.* from TEMPLATE TEMP left join HAVE HAVE on HAVE.DATEOFJOIN <= TEMP.DT <= ifn(HAVE.DATEOFRELIEVING=.,&HIGH.,HAVE.DATEOFRELIEVING); quit;
If I read your intention right, this will give you the headcount for every department and month:
data have;
infile cards dsd dlm=',';
input id dateofjoin :mmddyy8. dateofrelieving :mmddyy8. gender :$1. location :$2. vertical :$2.;
cards;
2231,04/03/13,09/01/16,F,NY,A
5012,11/19/13,,M,NY,B
5407,12/01/14,,M,NY,B
5871,05/08/14,09/02/16,F,NY,B
6388,09/29/14,,F,NY,BT
6662,11/05/14,09/02/16,M,NY,C
6675,11/06/14,,M,NY,B
7436,03/04/15,,M,ND,B
7944,05/04/15,,M,ND,B
8467,06/29/15,09/02/16,F,NY,B
8888,07/27/15,,F,ND,B
9207,09/01/15,,M,NY,S
10599,02/15/16,,M,NY,BT
10995,3/31/16,09/02/16,F,NY,B
11957,07/04/16,09/02/16,M,NY,C
12657,08/29/16,09/02/16,F,ND,C
;
run;
%let cutoffdate=%sysfunc(date());
data int;
set have;
if dateofrelieving = . then dateofrelieving = &cutoffdate;
format month mmddyy10.;
month = intnx('month',dateofjoin,0,'begin');
do while (month <= dateofrelieving);
output;
month = intnx('month',month,1,'begin');
end;
drop dateofjoin dateofrelieving;
run;
proc sql;
create table manforce as
select count(*) as headcount, location, gender, vertical, month
from int
group by location, gender, vertical, month
;
quit;
(Also note how to post example data; nobody in his right mind opens Office files from the web)
You could now calculate attrition values using the lag() function for the previous month's headcount.
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.
If you have a SAS data set then the instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to get the text of a data step to duplicate your example data.
You should only include needed variables to exercise your requirements and enough cases to demonstrate the logic. It is also a very good idea to show the desired result for the example data.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.