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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.