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
What does input data look like?
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
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
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
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
Thank you
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
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.