DATA Step, Macro, Functions and more

Do Loops over months

Reply
Contributor
Posts: 25

Do Loops over months

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.

 

Super User
Super User
Posts: 7,413

Re: Do Loops over months

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;
Super User
Posts: 6,964

Re: Do Loops over months

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,538

Re: Do Loops over months

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.

Ask a Question
Discussion stats
  • 3 replies
  • 349 views
  • 2 likes
  • 4 in conversation