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;
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"
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"
can you be more specific retain ___?
sorry, before the set statement put "retain cum_days". I'm surprised it worked at all without it(?)
@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.
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 |
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;
That was perfect! Thanks!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.