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

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
cbrotz
Pyrite | Level 9

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

ballardw
Super User

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.

 

 

cbrotz
Pyrite | Level 9

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

 

art297
Opal | Level 21

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

cbrotz
Pyrite | Level 9

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.

 

ballardw
Super User

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

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
  • 6 replies
  • 921 views
  • 2 likes
  • 3 in conversation