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
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
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
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.
Type was just part of how I was grouping the data. I probably did not need to include it in the sample data.....
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
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.
SQL is generally a pain if the Order of data records processed gets involved which this bit used extensively.
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.