BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

Hi I have a 2 tables that 

 

 Machine.     Time.                      Date 

 

A1.               15:35:35         11/07/15

A2                10:59:06        11/04/15

A1.                 15:42:23.       11/07/15

 

Table2

 

 Machine.     Time.                      Date 

A1.               15:38:35         11/07/15

A2                10:55:06        11/04/15

A3               15:45:23.       11/07/15

 

I need to look into both tables an keep the entries that are in one table an not the other.if there are 2 entries that have same date ,same machine an the time falls between 15 min of each table exclude one of the 2...the output shoult look like this 

Machine.     Time.                       Date 

   A3               15:45:23.       11/07/15.                                     

A1.               15:35:35         11/07/15

A2.               10:55:06        11/04/15     

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

I suggest to use datetimes. It makes the code simpler and allows matching times just before and after midnight :

 

data t1;
input Machine $ Time :time8. Date :mmddyy8.;
datalines; 
A1               15:35:35         11/07/15
A2                10:59:06        11/04/15
A1                 15:42:23       11/07/15
;
data t2;
input Machine $ Time :time8. Date :mmddyy8.;
datalines; 
A1               15:38:35         11/07/15
A2                10:55:06        11/04/15
A3               15:45:23       11/07/15
;

/* Concatenate the tables, create datetimes */
data t;
set t1 t2;
dt = dhms(date, hour(time), minute(time), second(time));
format dt datetime19. date date9. time time8.;
run;

proc sort data=t; by machine dt; run;

/* Drop records that are less than 15 min. from previous */
data want;
do until(last.machine);
    set t; by machine;
    if dt - '00:15:00't > lastDT then do;
        output;
        lastDT = dt;
        end;
    end;
drop lastDT;
run;

proc print data=want noobs; run;
PG

View solution in original post

4 REPLIES 4
BETO
Fluorite | Level 6
If it falls greater than 15 min keep both of them. .
PGStats
Opal | Level 21

I suggest to use datetimes. It makes the code simpler and allows matching times just before and after midnight :

 

data t1;
input Machine $ Time :time8. Date :mmddyy8.;
datalines; 
A1               15:35:35         11/07/15
A2                10:59:06        11/04/15
A1                 15:42:23       11/07/15
;
data t2;
input Machine $ Time :time8. Date :mmddyy8.;
datalines; 
A1               15:38:35         11/07/15
A2                10:55:06        11/04/15
A3               15:45:23       11/07/15
;

/* Concatenate the tables, create datetimes */
data t;
set t1 t2;
dt = dhms(date, hour(time), minute(time), second(time));
format dt datetime19. date date9. time time8.;
run;

proc sort data=t; by machine dt; run;

/* Drop records that are less than 15 min. from previous */
data want;
do until(last.machine);
    set t; by machine;
    if dt - '00:15:00't > lastDT then do;
        output;
        lastDT = dt;
        end;
    end;
drop lastDT;
run;

proc print data=want noobs; run;
PG
BETO
Fluorite | Level 6
Hi pgstats. How would it look like if I wanted to keep everything within 15min? ... If it was greater than 15 min drop.. Table one columns would be machine time date. Table 2 machine start date end date duration time
PGStats
Opal | Level 21

Post as a new question with example data and expected output.

PG

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 948 views
  • 1 like
  • 2 in conversation