BookmarkSubscribeRSS Feed
makarand
Obsidian | Level 7

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

IDDateofJoinDateOfRelievingGenderLocationVertical
223104/03/1309/01/16FNYA
501211/19/13 MNYB
540712/01/14 MNYB
587105/08/1409/02/16FNYB
638809/29/14 FNYBT
666211/05/1409/02/16MNYC
667511/06/14 MNYB
743603/04/15 MNDB
794405/04/15 MNDB
846706/29/1509/02/16FNYB
888807/27/15 FNDB
920709/01/15 MNYS
1059902/15/16 MNYBT
1099503/31/1609/02/16FNYB
1195707/04/1609/02/16MNYC
1265708/29/1609/02/16FNDC

 

 

output I am expecting;

MonthLocationVerticalGenderHeadcountAttrition

 

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.

 

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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.

ballardw
Super User

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.

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
  • 3 replies
  • 1740 views
  • 2 likes
  • 4 in conversation