DATA Step, Macro, Functions and more

Counting number of days for each employee

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Counting number of days for each employee

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;

 

 


Accepted Solutions
Solution
‎06-18-2018 11:45 AM
Regular Contributor
Posts: 162

Re: Counting number of days for each employee

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"

 

--------------
blog: papersandprograms.com

View solution in original post


All Replies
Solution
‎06-18-2018 11:45 AM
Regular Contributor
Posts: 162

Re: Counting number of days for each employee

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"

 

--------------
blog: papersandprograms.com
Contributor
Posts: 35

Re: Counting number of days for each employee

Posted in reply to PaulBrownPhD

can you be more specific retain ___?

Regular Contributor
Posts: 162

Re: Counting number of days for each employee

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

--------------
blog: papersandprograms.com
Super User
Posts: 13,583

Re: Counting number of days for each employee


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

 

Contributor
Posts: 35

Re: Counting number of days for each employee

An employee may have multiple records on any given day.


Contributor
Posts: 35

Re: Counting number of days for each employee

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

Re: Counting number of days for each employee

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;

Contributor
Posts: 35

Re: Counting number of days for each employee

Posted in reply to Astounding

That was perfect!  Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 149 views
  • 1 like
  • 4 in conversation