BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

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

7 REPLIES 7
PGStats
Opal | Level 21

What does input data look like?

PG
BETO
Fluorite | Level 6

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
Opal | Level 21

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

PG
BETO
Fluorite | Level 6

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
Opal | Level 21

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

PG
BETO
Fluorite | Level 6

Thank you

BETO
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 895 views
  • 0 likes
  • 2 in conversation