Count date sequence

[BETO]

HI I have a table that tracks compliance results.

what I'm needing is a count of days the compliance has failed greater than 30 days .once a compliance has been done successfully it falls from report. In my table I  determine compliance with a yes or no 

for example

TID1 last time compliance done.   Count of date. 

1          08/01/2013.                        40

once there is a compliance done than it falls out of the report

THanks for your assistance

[PGStats]

What does input data look like?

[BETO]

I have three columns

date of compliance | compliance results| compliance on

08/01/13.                             No.                     T1

08/05/13.                              Yes.                   T2

08/07/13.                                No.                   T1

i need to know how many date of last yes compliance and once a yes compliance has register fall of the report..

date of compliance | compliance results| compliance on |. Count of days

08/01/13.                             No.                     T1.                          38

08/07/13.                                No.                   T1.                          31

[PGStats]

How about something like this :

data have;
input dateComp :mmddyy8. comp $ compOn $;
format dateComp mmddyy8.;
datalines;
08/01/13                             No                     T1
08/05/13                              Yes                   T2
08/07/13                                No                   T1
;

data want;
set have;
if upcase(comp) ne "YES";
countDays = intck("DAY", dateComp, today());
run;

proc print data=want noobs; run;

PG

[BETO]

Proc SQL;

CREATE TABLE Table1 AS

(select

Date,

Vendor_,

VendorBranch,

ID,

CompCheck,

Service,

CompCheck1,

date1,

Max(date1) As Last_End_Dt

From Want1

Where CompCheck1 ="No"

And countDays >=30

GROUP BY

date1

);

Quit;

HI PgStats

ENclose is my table with your help I made it work..I realized that it brings in every No for each date I need the last day so I tried using a max date or max count but it still brings everything for example

T1   No.     08/10/13

T1. No.        08/15/13

i would like to see only 08/10/13

[PGStats]

I don't quite understand, but I think you need to add a HAVING clause, like this:

Proc SQL;

Create Table Want2 As

Select

     Date,

     Vendor_,

     VendorBranch,

     ID,

     CompCheck,

     Service,

     CompCheck1,

     date1

From Want1

Where CompCheck1 ="No" And countDays >=30

Group By ID

Having date1=Max(date1);

Quit;

Also, the CompCheck1="No" condition might not be needed.

PG

[BETO]

Thank you

[BETO]

HI PGstats,

i must have jump the gun on this last night been under the weather for a while...

The assistance you provided does exactly what I requested an I thank you for that. After checking results and validating the raw data I missed something

what I'm looking for the code to do is to track how long ago was there a yes compliance by counting the # of no tin between the last yes compliance. Once there is a yes it falls from. The audit report.

TI.  | Date. Compliance |count of days|  compliance

T1.       08/01/13.                   40.                 No

it excludes all other. T1.  After the 08/01/13 if they are No compliance an just keep the running sum of days  in this case 40 until we get a Yes compliance.    Thanks let me know if I have to open a new qrequestrequest