topic Re: how to determine if service was done in SAS Procedures

how to determine if service was done

BETO — Wed, 26 Aug 2015 20:14:59 GMT

hi I have 3 tables

1st table shows if service was missed data is pulling for the pervious month .it looks like this

ID. date. Complinace

a1. 07/01/15. No

a2. 07/05/15. No

a2. 07/09/15. No

a3. 07/10/15. No

2nd table shows if there was a ticket open

ID. Date. Tick

a1. 07/05/15. 123

a2. 07/8/15. 124

a3. 07/14/15. 126

the 3rd table would show all the services process in a month...what happens if service is missed vendor needs to go back. To service Machine ...but if they don't than there is a ticket that is open base on table 2

id. Date. Type

a1. 07/03/15. service

a2. 07/07/15. Service

THe output. That I'm looking for is

only when there is match between table 1 and table 2 and doesn't exist on table 3 ... I hope it make sense thanks for assistance

Re: how to determine if service was done

ballardw — Wed, 26 Aug 2015 21:52:38 GMT

Have you attempted to combine the first two datasets yet?

Re: how to determine if service was done

BETO — Wed, 26 Aug 2015 22:02:45 GMT

hi thanks for responding I thought of that but there could be more one occurances of ID an differ date...

the 2d table would id but the date will not be like for like...

Re: how to determine if service was done

BETO — Wed, 26 Aug 2015 22:09:17 GMT

sorry screen froze the 3rd table will show if vendor went back after the miss service date to determine if machine was service if I was then it's good it's when it wasn't an it generates a tick is what I need to output....tthe issue is how to connect tables when id is same but date is differ within a 7 day window thanks for help

Re: how to determine if service was done

Ksharp — Thu, 27 Aug 2015 12:56:55 GMT

You don't post the output you want yet ?

proc sql;

select id,date from table1

intersect

select id,date from table2

except

select id,date from table3

;

quit;

Re: how to determine if service was done

BETO — Thu, 27 Aug 2015 13:59:19 GMT

Xia,

Sorry the output that I'm looking for is table 1 and table 2 table 3 is my filter

output

from 1st table. From 2nd table From 3rd table

id. Date compliance Tick Blank. I would report on that

id date. Compliance. Tick. Service. I would not report on that.....

here is what I want to do if a machine was not service let say on 8/01/15. An on 8/4/15 it generates a ticket than I would report it

different secnario

machine is is not service on 08/1/15. And on 08/3/15 it gets service ThAt gets excluded....

THanks for help