Counting number of days for each employee

[pangea17]

I am using SAS 9.4 and enterprise guide 7.1.

I have a database with a list of employees, each one of them has a unique employee_id. They get a record for each time they make a phone call.  I am trying to create a variable that will count the number of dials that they make, and the total number of days that they work during a given period. I sorted by employee_id and dial_date respectively. The first data step seems to give me the total number of calls made.  The second data step doesn't give me the number of days worked.  I was thinking it would start with employee 1 and march 1 as the first observation and then each time the date changed it would add one to the cum_days variable. But, it is giving me the same number as the first data step.

data test1;

format dials cum_days 8.;

  set basedata;

by employee_id dial_date;

if first.employee_id then dials=0;

dials+1;

if last.employee_id;

RUN;

data test2;

format dials cum_days 8.;

  set basedata;

by employee_id dial_date;

if first.employee_id and first.dial_date then cum_days =0;

cum_days+1;

if last.employee_id and last.dial_date;

RUN;

[pau13rown · Accepted solution]

you need a retain statement, also in the second data step change "if first.employee_id and first.dial_date then" to just "if first.dial_date then" but keep the by statement as "by employee_id dial_date"

[pangea17]

can you be more specific retain ___?

[pau13rown]

sorry, before the set statement put "retain cum_days". I'm surprised it worked at all without it(?)

[ballardw]

---

@pangea17 wrote:

I am using SAS 9.4 and enterprise guide 7.1.

I have a database with a list of employees, each one of them has a unique employee_id. They get a record for each time they make a phone call.  I am trying to create a variable that will count the number of dials that they make, and the total number of days that they work during a given period. I sorted by employee_id and dial_date respectively. The first data step seems to give me the total number of calls made.  The second data step doesn't give me the number of days worked.  I was thinking it would start with employee 1 and march 1 as the first observation and then each time the date changed it would add one to the cum_days variable. But, it is giving me the same number as the first data step.

---

With a requirement like days within a given period I have to ask if your date variable is a SAS date value or not. Otherwise any comparison to identify a period may be very iffy.

This should give a count but it will only reflect the days with a dial_date. If the employee works without that then another approach.

proc sql;
   create table test2 as
   select employee_id, count(*) as daysworked
   from (select distinct employee_id,dial_date from basedata)
   ;
quit; 

You did not say whether or not a single employee may have more than one record per dial_date or not.

[pangea17]

An employee may have multiple records on any given day.

[pangea17]

This works, but I was hoping there would be something better.

proc sql;

create table test2 as

select employee_id, count(*) as cum_days

from (select distinct employee_id, dialdate from mercury);

QUIT;

data test3 (keep=employee_id days);

set test2;

by employee_id;

if first.employee_id then days=0;

days+1;

if last.employee_id;

RUN;

proc freq data= test3;

tables days;

RUN;

days	Frequency	Percent	Cumulative Frequency	Cumulative Percent
1	127	7.53	127	7.53
2	81	4.80	208	12.34
3	169	10.02	377	22.36
4	423	25.09	800	47.45
5	678	40.21	1478	87.66
6	192	11.39	1670	99.05
7	16	0.95	1686	100.00

[Astounding]

Here's a variation that uses similar tools to those you began with:

data test2;

  set basedata;

  by employee_id dial_date;

  if first.employee_id then cum_days =0;

  if first.dial_date then cum_days+1;

  if last.employee_id;

run;

[pangea17]

That was perfect!  Thanks!