BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pangea17
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pau13rown
Lapis Lazuli | Level 10

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"

 

View solution in original post

8 REPLIES 8
pau13rown
Lapis Lazuli | Level 10

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
Quartz | Level 8

can you be more specific retain ___?

pau13rown
Lapis Lazuli | Level 10

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

ballardw
Super User

@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
Quartz | Level 8
An employee may have multiple records on any given day.


pangea17
Quartz | Level 8

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
PROC Star

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
Quartz | Level 8

That was perfect!  Thanks!

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
  • 8 replies
  • 941 views
  • 1 like
  • 4 in conversation