09-07-2013 10:34 PM
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
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
09-07-2013 11:56 PM
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
09-08-2013 12:13 AM
How about something like this :
input dateComp :mmddyy8. comp $ compOn $;
format dateComp mmddyy8.;
08/01/13 No T1
08/05/13 Yes T2
08/07/13 No T1
if upcase(comp) ne "YES";
countDays = intck("DAY", dateComp, today());
proc print data=want noobs; run;
09-08-2013 08:03 PM
CREATE TABLE Table1 AS
Max(date1) As Last_End_Dt
Where CompCheck1 ="No"
And countDays >=30
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
09-08-2013 10:19 PM
I don't quite understand, but I think you need to add a HAVING clause, like this:
Create Table Want2 As
Where CompCheck1 ="No" And countDays >=30
Group By ID
Also, the CompCheck1="No" condition might not be needed.
09-09-2013 10:19 AM
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