topic Re: Creating a New Retention Flag in SAS Programming https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350134#M81350 <P>Yes I needed to to some more coding but this was a great start.&nbsp; The data had some 0s in it which messes up the counter but thank you very much!&nbsp; I was thinking of using SQL and I think using SAS saved quite a bit of coding.</P><P>&nbsp;</P> Fri, 14 Apr 2017 18:35:44 GMT cbrotz 2017-04-14T18:35:44Z Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349866#M81255 <P>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.&nbsp; The data below is one group with a key of date type and name - the flag is what I am trying to create.</P><P>I am thinking to use a retain statement but I am just having a mental block as to how to code that flag.&nbsp; Any ideas would be much appreciated.</P><P>&nbsp;</P><P>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Billings&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Flag</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>201501&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;1015.12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; New<BR />201502&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;1539.32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201503&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;2686.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201504&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;3950.2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201505&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;4512.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201506&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;5033.25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201507&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;5622.05<BR />201508&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;6111.05<BR />201509&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;6609.04<BR />201510&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;7731.36<BR />201511&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;8207.39<BR />201512&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;8358.19<BR />201601&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;9629.1<BR />201602&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;9907.63<BR />201603&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;10072.85<BR />201604&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;10596.35<BR />201605&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;11399.95<BR />201606&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;11712.15<BR />201607&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;12086.63<BR />201608&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;13154.01<BR />201609&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;13832.43<BR />201610&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;14616.21<BR />201611&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;15034.52<BR />201612&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;15898.62<BR />201701&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;16974.13</P> Thu, 13 Apr 2017 20:00:54 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349866#M81255 cbrotz 2017-04-13T20:00:54Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349877#M81261 <P>Just to clarify this is an example if there were no billings for a given time:</P><P>&nbsp;</P><P>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Billings&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Flag</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>201501&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;1015.12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; New<BR />201502&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;1539.32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201503&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;2686.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201504&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;3950.2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201505&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;4512.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201506&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;5033.25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201507&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201508&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201509&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201510&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201511&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201512&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201601&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201602&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201603&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201604&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201605&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201606&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201607&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;12086.63 &nbsp; &nbsp; New<BR />201608&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;13154.01&nbsp;&nbsp;&nbsp;&nbsp; Retention<BR />201609&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;13832.43<BR />201610&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;14616.21<BR />201611&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;15034.52<BR />201612&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;15898.62<BR />201701&nbsp;&nbsp; &nbsp;3000011&nbsp;&nbsp; &nbsp;ANNUAL VEHICLE INSPECTION&nbsp;&nbsp; &nbsp;16974.13</P> Thu, 13 Apr 2017 20:35:16 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349877#M81261 cbrotz 2017-04-13T20:35:16Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349899#M81273 <P>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.</P> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 13 Apr 2017 22:40:54 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349899#M81273 ballardw 2017-04-13T22:40:54Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349900#M81274 <P>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:</P> <P>&nbsp;</P> <PRE>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; </PRE> <P>Art, CEO, AnalystFinder.com</P> Thu, 13 Apr 2017 22:41:01 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/349900#M81274 art297 2017-04-13T22:41:01Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350134#M81350 <P>Yes I needed to to some more coding but this was a great start.&nbsp; The data had some 0s in it which messes up the counter but thank you very much!&nbsp; I was thinking of using SQL and I think using SAS saved quite a bit of coding.</P><P>&nbsp;</P> Fri, 14 Apr 2017 18:35:44 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350134#M81350 cbrotz 2017-04-14T18:35:44Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350136#M81351 <P>Type was just part of how I was grouping the data.&nbsp; I probably did not&nbsp; need to include it in the sample data.....</P><P>&nbsp;</P> Fri, 14 Apr 2017 18:37:32 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350136#M81351 cbrotz 2017-04-14T18:37:32Z Re: Creating a New Retention Flag https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350162#M81360 <P>SQL is generally a pain if the Order of data records processed gets involved which this bit used extensively.</P> Fri, 14 Apr 2017 19:04:13 GMT https://communities.sas.com/t5/SAS-Programming/Creating-a-New-Retention-Flag/m-p/350162#M81360 ballardw 2017-04-14T19:04:13Z