topic Re: Counting number of days for each employee in SAS Programming

Counting number of days for each employee

pangea17 — Fri, 15 Jun 2018 23:02:32 GMT

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;

Re: Counting number of days for each employee

pau13rown — Fri, 15 Jun 2018 23:16:47 GMT

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"

Re: Counting number of days for each employee

pangea17 — Fri, 15 Jun 2018 23:26:38 GMT

can you be more specific retain ___?

Re: Counting number of days for each employee

ballardw — Fri, 15 Jun 2018 23:28:46 GMT

@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.

Re: Counting number of days for each employee

pangea17 — Fri, 15 Jun 2018 23:31:13 GMT

An employee may have multiple records on any given day.

Re: Counting number of days for each employee

pau13rown — Fri, 15 Jun 2018 23:41:53 GMT

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

Re: Counting number of days for each employee

pangea17 — Sat, 16 Jun 2018 00:34:25 GMT

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

Re: Counting number of days for each employee

Astounding — Sat, 16 Jun 2018 03:48:16 GMT

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;

Re: Counting number of days for each employee

pangea17 — Mon, 18 Jun 2018 15:47:05 GMT

That was perfect! Thanks!