Desktop productivity for business analysts and programmers

active activity counts by month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

active activity counts by month

 

I have Enterprise guide 7.1

I have a table, which looks like this:

activity  startdate       enddate

act 1     01-10-2016   15-01-2017

act 2     24-02-2015   

act 1     06-05-2014    23-06-2016

act 3     20-10-2016   23-04-2017

 

and I want a table, which looks like:

 

activity 01-2016  02-2016  03-2016  04-2016  05-2016 ....  03-2017

act 1      1              3               3             2              1                  6

act 2      3              4               5             2              2                  1

act 3      1              1               2             1              0                  2

 

How can transform first table to the second table? I would really like to make it, so it continues 'forever'?

Thank you Smiley Happy

 

 


Accepted Solutions
Solution
‎05-03-2017 06:13 AM
Grand Advisor
Posts: 9,576

Re: active activity counts by month

Fix it for the missing end_date.



data have;
    infile cards truncover;
    informat activity $8. date_start date_end ddmmyy10.;
    format date_: date9.;
    input activity $  date_start date_end;
    cards;
act1     01-10-2016   15-01-2017
act2     24-02-2015   
act1     06-05-2014    23-06-2016
act3     20-10-2016   23-04-2017
act1     20-01-2016   01-04-2017
;
run;
data temp;
 set have;
 if not missing(date_start) and not missing(date_end) then do;
  do i=date_start to date_end;
    month=month(i);
    if month ne lag(month) then output;
  end;
 end;
  else if  missing(date_end) then do;
   do i=date_start to today();
    month=month(i);
    if month ne lag(month) then output;
   end;
  end;

format i date9.;
keep activity i ;
run;
proc freq data=temp noprint;
table activity*i/out=temp1 list;
format i monyy7.;
run;
proc transpose data=temp1 out=want(drop=_:);
by activity;
var count;
id i;
run;

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,292

Re: active activity counts by month

A couple of questions:

 

1. What do the numbers under the various date columns represet?

2, Do you want the columns to represent month-year?

3. What do you want to show when there is a start date, but no end date?

 

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 7

Re: active activity counts by month

1. It counts how many observations of that activity has been active that specific month represented in the table. -
2. Columbs should represent á specific month in a specific year
3. My first table continously update, so I need the activity to count, when there is no enddate (still active) I need my table to take future periods in to account when they occur. When we go into May, then May will appear in table
Is it possible to make such a table?
Occasional Contributor
Posts: 7

Re: active activity counts by month

The table should look like this:
Activity 01-2016 02-2016 ... 04-2017
Act 1 3 2 ... 3
Act 2. 2 3 .... 4
Act 3 1 2 ... 6

01-2016 represent january in 2016, 02-2017 february in 2016and so forth. The dots represent the months inbetween.
Trusted Advisor
Posts: 1,052

Re: active activity counts by month

Not exhaustively tested:

 

data Inter1(keep=activity IntervalDate);

set Have;

if missing(enddate) then

enddate = startdate;

startdate = intnx('month', startdate, 0, 'b');

enddate = intnx('month', enddate, 0, 'b');

format IntervalDate date9.;

IntervalDate = startdate;

StopFlag = 0;

do while(^StopFlag);

output;

if IntervalDate = enddate then

StopFlag = 1;

IntervalDate = intnx('month', IntervalDate, 1, 'b');

end;

run;

proc means data=Inter1 nway noprint;

class activity IntervalDate;

var IntervalDate;

output out=Inter2(drop=_TYPE_ _FREQ_) n=Count;

run;

/* Get a list of unique activity names */

proc sql noprint;

create table Names as

select distinct i.activity from Inter2 i;

quit;

/* Generate every first of the month from Jan 1 2014 to Dec 31 2017 */

data AllMonths;

do Year = 2014 to 2017;

do Month = 1 to 12;

DateToCheck = mdy(Month, 1, Year);

format DateToCheck date9.;

keep DateToCheck;

output;

end;

end;

/* Cartesian join to get a list of months and company names */

proc sql noprint;

create table AllMonthsNames as

select n.activity, a.DAteToCheck from Names n cross join AllMonths a;

quit;

/* Join the two, to get a full list of months with Type */

proc sql noprint;

create table Inter3 as

select i.*, a.DateToCheck from Inter2 i right join AllMonthsNames a on(i.IntervalDate = a.DateToCheck and i.activity = a.activity)

order by a.activity, a.DateToCheck;

quit;

/* Ripple the company names and fill in the zero counts both before and after the actual data */

data Inter3(drop=_Smiley Happy;

set Inter3(drop=IntervalDate);

length _OldActivity $5;

retain _OldActivity _OldDate;

if _n_ = 1 then do;

_OldActivity = activity;

_OldDate = DateToCheck;

end;

else if DateToCheck < _OldDate then _OldActivity = activity;

if missing(activity) then

activity = _OldActivity;

else _OldActivity = activity;

if missing(Count) then

Count = 0;

_OldDate = DateToCheck;

SeqNum = _n_; /* We need this to turn the dataset upside down */

run;

/* Sort descsending to ripple early records */

proc sort data=Inter3;

by descending SeqNum;

run;

data Inter3(drop=_Smiley Happy;

set Inter3;

length _OldActivity $5;

retain _OldActivity _OldDate;

if _n_ = 1 then do;

_OldActivity = activity;

_OldDate = DateToCheck;

end;

else if DateToCheck > _OldDate then _OldActivity = activity;

if missing(activity) then

activity = _OldActivity;

else _OldActivity = activity;

_OldDate = DateToCheck;

format DateToCheck yymm5.;

run;

proc sort data=Inter3 out=Inter3(drop=SeqNum);

by SeqNum;

run;

proc transpose data=Inter3 out=Want(drop=_NAME_) prefix=D;

by activity;

id DateToCheck;

var Count;

run;

Occasional Contributor
Posts: 7

Re: active activity counts by month

Sorry, i cannot make the table better. Act 1 represent the first activity, where 3 observations were maid in 01-2016 and 2 observations were maid in 02-2016, and so fourth
Occasional Contributor
Posts: 7

Re: active activity counts by month

Thank you so much 😊😊😊
Grand Advisor
Posts: 17,327

Re: active activity counts by month

I think something like this may work as well, not sure how much control you need but I suspect PROC TABULATE will generate the table you desire. 

 

*create sample data;

data have;
    infile cards truncover;
    informat activity $8. date_start date_end ddmmyy10.;
    format date_: date9.;
    input activity $  date_start date_end;
    cards;
act1     01-10-2016   15-01-2017
act2     24-02-2015   
act1     06-05-2014    23-06-2016
act3     20-10-2016   23-04-2017
act1     20-01-2016   01-04-2017
;
run;

data long;
    set have;
    *calculate the end date, use today if no end date;
    date_end=coalesce(date_end, date());
    *calculate the number of months of 'activity';
    nmonths=intck('month', date_start, date_end, 'D');
    *create  record for every month;

    do i=0 to nmonths;
        Month=intnx('month', date_start, i, 'b');
        output;
    end;
    format month yymon7.;
    drop nmonths date_start date_end;
run;

proc tabulate data=long;
    where year(month)=2016;
    *limit to specific year for demo;
    class activity month;
    table activity, month*n / misstext='0';
    
run;
Grand Advisor
Posts: 9,576

Re: active activity counts by month


data have;
    infile cards truncover;
    informat activity $8. date_start date_end ddmmyy10.;
    format date_: date9.;
    input activity $  date_start date_end;
    cards;
act1     01-10-2016   15-01-2017
act2     24-02-2015   
act1     06-05-2014    23-06-2016
act3     20-10-2016   23-04-2017
act1     20-01-2016   01-04-2017
;
run;
data temp;
 set have;
 if not missing(date_start) and not missing(date_end) then do;
  do i=date_start to date_end;
    month=month(i);
    if month ne lag(month) then output;
  end;
 end;
 else if not missing(date_start) then do;i=date_start;output;end;
  else if not missing(date_end) then do;i=date_end;output;end;

format i date9.;
keep activity i ;
run;
proc freq data=temp noprint;
table activity*i/out=temp1 list;
format i monyy7.;
run;
proc transpose data=temp1 out=want(drop=_:);
by activity;
var count;
id i;
run;


Solution
‎05-03-2017 06:13 AM
Grand Advisor
Posts: 9,576

Re: active activity counts by month

Fix it for the missing end_date.



data have;
    infile cards truncover;
    informat activity $8. date_start date_end ddmmyy10.;
    format date_: date9.;
    input activity $  date_start date_end;
    cards;
act1     01-10-2016   15-01-2017
act2     24-02-2015   
act1     06-05-2014    23-06-2016
act3     20-10-2016   23-04-2017
act1     20-01-2016   01-04-2017
;
run;
data temp;
 set have;
 if not missing(date_start) and not missing(date_end) then do;
  do i=date_start to date_end;
    month=month(i);
    if month ne lag(month) then output;
  end;
 end;
  else if  missing(date_end) then do;
   do i=date_start to today();
    month=month(i);
    if month ne lag(month) then output;
   end;
  end;

format i date9.;
keep activity i ;
run;
proc freq data=temp noprint;
table activity*i/out=temp1 list;
format i monyy7.;
run;
proc transpose data=temp1 out=want(drop=_:);
by activity;
var count;
id i;
run;

Occasional Contributor
Posts: 7

Re: active activity counts by month

Hi again

 

I can see, that the programming is restricted to the table i maid. It is an example of the data set i have. 

To be more specific, i have 30 activities and each activity has 30 or more observations, that I wish to count.

 

The end_date has '.' and is not missing, when it is still active.  

 

Thank you sooo much for your time and help - it is appreciated Smiley Happy 

Trusted Advisor
Posts: 1,052

Re: active activity counts by month

My code should work fine under the conditions you specify, you just need to adjust the start and end years to accomodate your range of dates.

Please be a little more clear on what it means when end date is missing.

Tom

Occasional Contributor
Posts: 7

Re: active activity counts by month

when the activity is still active, the end_date is specified with at dot ( . ) in the dataset.
Trusted Advisor
Posts: 1,052

Re: active activity counts by month

I had assumed that a missing enddate implied it was a one-day activity.


In my code, replace

 

if missing(enddate) then enddate = startdate;

 

with

 

if missing(enddate) then enddate = today();

 

Tom

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 159 views
  • 1 like
  • 5 in conversation