topic Re: Count date sequence in SAS Procedures

Count date sequence

BETO — Sun, 08 Sep 2013 02:34:55 GMT

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

Re: Count date sequence

PGStats — Sun, 08 Sep 2013 03:21:05 GMT

What does input data look like?

Re: Count date sequence

BETO — Sun, 08 Sep 2013 03:56:06 GMT

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

Re: Count date sequence

PGStats — Sun, 08 Sep 2013 04:13:25 GMT

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;

Re: Count date sequence

BETO — Mon, 09 Sep 2013 00:03:41 GMT

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

Re: Count date sequence

PGStats — Mon, 09 Sep 2013 02:19:45 GMT

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.

Re: Count date sequence

BETO — Mon, 09 Sep 2013 03:47:30 GMT

Thank you

Re: Count date sequence

BETO — Mon, 09 Sep 2013 14:19:25 GMT

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