DATA Step, Macro, Functions and more

Creating a New Retention Flag

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Creating a New Retention Flag

In the following data I am trying to tag billings as retention if there has been any billings in the past 12 months for that group.  The data below is one group with a key of date type and name - the flag is what I am trying to create.

I am thinking to use a retain statement but I am just having a mental block as to how to code that flag.  Any ideas would be much appreciated.

 

Date         Type          Name                                             Billings         Flag

 

 

 

201501    3000011    ANNUAL VEHICLE INSPECTION    1015.12       New
201502    3000011    ANNUAL VEHICLE INSPECTION    1539.32       Retention
201503    3000011    ANNUAL VEHICLE INSPECTION    2686.45       Retention
201504    3000011    ANNUAL VEHICLE INSPECTION    3950.2         Retention
201505    3000011    ANNUAL VEHICLE INSPECTION    4512.1         Retention
201506    3000011    ANNUAL VEHICLE INSPECTION    5033.25       Retention
201507    3000011    ANNUAL VEHICLE INSPECTION    5622.05
201508    3000011    ANNUAL VEHICLE INSPECTION    6111.05
201509    3000011    ANNUAL VEHICLE INSPECTION    6609.04
201510    3000011    ANNUAL VEHICLE INSPECTION    7731.36
201511    3000011    ANNUAL VEHICLE INSPECTION    8207.39
201512    3000011    ANNUAL VEHICLE INSPECTION    8358.19
201601    3000011    ANNUAL VEHICLE INSPECTION    9629.1
201602    3000011    ANNUAL VEHICLE INSPECTION    9907.63
201603    3000011    ANNUAL VEHICLE INSPECTION    10072.85
201604    3000011    ANNUAL VEHICLE INSPECTION    10596.35
201605    3000011    ANNUAL VEHICLE INSPECTION    11399.95
201606    3000011    ANNUAL VEHICLE INSPECTION    11712.15
201607    3000011    ANNUAL VEHICLE INSPECTION    12086.63
201608    3000011    ANNUAL VEHICLE INSPECTION    13154.01
201609    3000011    ANNUAL VEHICLE INSPECTION    13832.43
201610    3000011    ANNUAL VEHICLE INSPECTION    14616.21
201611    3000011    ANNUAL VEHICLE INSPECTION    15034.52
201612    3000011    ANNUAL VEHICLE INSPECTION    15898.62
201701    3000011    ANNUAL VEHICLE INSPECTION    16974.13


Accepted Solutions
Solution
‎04-14-2017 02:33 PM
PROC Star
Posts: 7,363

Re: Creating a New Retention Flag

My guess is that your criteria are a bit more complex than you described, but here is a start that matches what you've shown us thus far:

 

data want;
  set have;
  length flag $9.;
  by type;
  if first.type then flag='New';
  else if Billings gt 0 then do;
    if counter ge 12 then flag='New';
    else flag='Retention';
    counter=0;
  end;
  else do;
    Counter+1;
    if counter le 12 then flag='Retention';
    else call missing(flag);
  end;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Contributor
Posts: 29

Re: Creating a New Retention Flag

Just to clarify this is an example if there were no billings for a given time:

 

Date         Type          Name                                             Billings         Flag

 

 

 

201501    3000011    ANNUAL VEHICLE INSPECTION    1015.12       New
201502    3000011    ANNUAL VEHICLE INSPECTION    1539.32       Retention
201503    3000011    ANNUAL VEHICLE INSPECTION    2686.45       Retention
201504    3000011    ANNUAL VEHICLE INSPECTION    3950.2         Retention
201505    3000011    ANNUAL VEHICLE INSPECTION    4512.1         Retention
201506    3000011    ANNUAL VEHICLE INSPECTION    5033.25       Retention
201507    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201508    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201509    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201510    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201511    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201512    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201601    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201602    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201603    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201604    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201605    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201606    3000011    ANNUAL VEHICLE INSPECTION    0                  Retention
201607    3000011    ANNUAL VEHICLE INSPECTION    12086.63     New
201608    3000011    ANNUAL VEHICLE INSPECTION    13154.01     Retention
201609    3000011    ANNUAL VEHICLE INSPECTION    13832.43
201610    3000011    ANNUAL VEHICLE INSPECTION    14616.21
201611    3000011    ANNUAL VEHICLE INSPECTION    15034.52
201612    3000011    ANNUAL VEHICLE INSPECTION    15898.62
201701    3000011    ANNUAL VEHICLE INSPECTION    16974.13

Super User
Posts: 10,500

Re: Creating a New Retention Flag

What role to Type and Name have on this process if any? One might assume that if there were a new Type that the "counting" might start over.

 

 

Contributor
Posts: 29

Re: Creating a New Retention Flag

Type was just part of how I was grouping the data.  I probably did not  need to include it in the sample data.....

 

Solution
‎04-14-2017 02:33 PM
PROC Star
Posts: 7,363

Re: Creating a New Retention Flag

My guess is that your criteria are a bit more complex than you described, but here is a start that matches what you've shown us thus far:

 

data want;
  set have;
  length flag $9.;
  by type;
  if first.type then flag='New';
  else if Billings gt 0 then do;
    if counter ge 12 then flag='New';
    else flag='Retention';
    counter=0;
  end;
  else do;
    Counter+1;
    if counter le 12 then flag='Retention';
    else call missing(flag);
  end;
run;

Art, CEO, AnalystFinder.com

Contributor
Posts: 29

Re: Creating a New Retention Flag

Yes I needed to to some more coding but this was a great start.  The data had some 0s in it which messes up the counter but thank you very much!  I was thinking of using SQL and I think using SAS saved quite a bit of coding.

 

Super User
Posts: 10,500

Re: Creating a New Retention Flag

SQL is generally a pain if the Order of data records processed gets involved which this bit used extensively.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 129 views
  • 2 likes
  • 3 in conversation