Re: proc sql except - customize

makset · Posted 09-24-2018 10:02 AM

Hello

I have a problem with deleting observations in a dataset that exist in another dataset.

I could use the following command:

proc sql;
create table test3 as
select * from test2
except 
select * from test1;
quit;

but the dataset test2 has 2 variables with the dataset test1 has one variable so the command proc sql except does not work.

and this is the first problem.

The second problem is more complicated and I hope I can explain it simply, namely:

if you look at the sets, the variable "date" is made up of 60 portions of datetime collection:

04FEB2014:18:15:00
04FEB2014:18:30:00
04FEB2014:18:45:00
04FEB2014:19:00:00
04FEB2014:19:15:00
04FEB2014:19:30:00
04FEB2014:19:45:00

27JAN2012:13:15:00
27JAN2012:13:30:00
27JAN2012:13:45:00
27JAN2012:14:00:00
27JAN2012:14:15:00
27JAN2012:14:30:00
27JAN2012:14:45:00

and so on

In this case 60 x 7 obs = 420 obs

and I'm interested only in the removal of observations in portions of 7.

You probably imagine that the probability in a larger set of data can be removed by mistake quite accidentally.

for example:

data test (keep = date);
set  test2;
run;

proc sql;
create table test3 as
select * from test
except 
select * from test1;
quit;

datasets test3 have 242 obs (242/7=34,57 error)

I hope that I expressed myself clearly
thank you for help

best regards

data test1;
infile datalines  dsd truncover;
length date 8;
informat date datetime20.;
format date datetime20.;
input date;
datalines;
04FEB2014:18:15:00
04FEB2014:18:30:00
04FEB2014:18:45:00
04FEB2014:19:00:00
04FEB2014:19:15:00
04FEB2014:19:30:00
04FEB2014:19:45:00
27JAN2012:13:15:00
27JAN2012:13:30:00
27JAN2012:13:45:00
27JAN2012:14:00:00
27JAN2012:14:15:00
27JAN2012:14:30:00
27JAN2012:14:45:00
01SEP2005:20:15:00
01SEP2005:20:30:00
01SEP2005:20:45:00
01SEP2005:21:00:00
01SEP2005:21:15:00
01SEP2005:21:30:00
01SEP2005:21:45:00
06MAY2004:16:15:00
06MAY2004:16:30:00
06MAY2004:16:45:00
06MAY2004:17:00:00
06MAY2004:17:15:00
06MAY2004:17:30:00
06MAY2004:17:45:00
04SEP2009:18:15:00
04SEP2009:18:30:00
04SEP2009:18:45:00
04SEP2009:19:00:00
04SEP2009:19:15:00
04SEP2009:19:30:00
04SEP2009:19:45:00
15MAR2007:19:15:00
15MAR2007:19:30:00
15MAR2007:19:45:00
15MAR2007:20:00:00
15MAR2007:20:15:00
15MAR2007:20:30:00
15MAR2007:20:45:00
15MAR2013:17:15:00
15MAR2013:17:30:00
15MAR2013:17:45:00
15MAR2013:18:00:00
15MAR2013:18:15:00
15MAR2013:18:30:00
15MAR2013:18:45:00
05NOV2008:11:15:00
05NOV2008:11:30:00
05NOV2008:11:45:00
05NOV2008:12:00:00
05NOV2008:12:15:00
05NOV2008:12:30:00
05NOV2008:12:45:00
26AUG2004:16:15:00
26AUG2004:16:30:00
26AUG2004:16:45:00
26AUG2004:17:00:00
26AUG2004:17:15:00
26AUG2004:17:30:00
26AUG2004:17:45:00
15MAR2013:17:15:00
15MAR2013:17:30:00
15MAR2013:17:45:00
15MAR2013:18:00:00
15MAR2013:18:15:00
15MAR2013:18:30:00
15MAR2013:18:45:00
03FEB2015:06:15:00
03FEB2015:06:30:00
03FEB2015:06:45:00
03FEB2015:07:00:00
03FEB2015:07:15:00
03FEB2015:07:30:00
03FEB2015:07:45:00
15APR2013:23:15:00
15APR2013:23:30:00
15APR2013:23:45:00
16APR2013:00:00:00
16APR2013:00:15:00
16APR2013:00:30:00
16APR2013:00:45:00
18APR2014:15:15:00
18APR2014:15:30:00
18APR2014:15:45:00
18APR2014:16:00:00
18APR2014:16:15:00
18APR2014:16:30:00
18APR2014:16:45:00
19MAY2011:08:15:00
19MAY2011:08:30:00
19MAY2011:08:45:00
19MAY2011:09:00:00
19MAY2011:09:15:00
19MAY2011:09:30:00
19MAY2011:09:45:00
09MAR2004:18:15:00
09MAR2004:18:30:00
09MAR2004:18:45:00
09MAR2004:19:00:00
09MAR2004:19:15:00
09MAR2004:19:30:00
09MAR2004:19:45:00
20SEP2006:08:15:00
20SEP2006:08:30:00
20SEP2006:08:45:00
20SEP2006:09:00:00
20SEP2006:09:15:00
20SEP2006:09:30:00
20SEP2006:09:45:00
04AUG2015:17:15:00
04AUG2015:17:30:00
04AUG2015:17:45:00
04AUG2015:18:00:00
04AUG2015:18:15:00
04AUG2015:18:30:00
04AUG2015:18:45:00
01AUG2011:16:15:00
01AUG2011:16:30:00
01AUG2011:16:45:00
01AUG2011:17:00:00
01AUG2011:17:15:00
01AUG2011:17:30:00
01AUG2011:17:45:00
15FEB2007:16:15:00
15FEB2007:16:30:00
15FEB2007:16:45:00
15FEB2007:17:00:00
15FEB2007:17:15:00
15FEB2007:17:30:00
15FEB2007:17:45:00
24FEB2011:04:15:00
24FEB2011:04:30:00
24FEB2011:04:45:00
24FEB2011:05:00:00
24FEB2011:05:15:00
24FEB2011:05:30:00
24FEB2011:05:45:00
25MAR2014:20:15:00
25MAR2014:20:30:00
25MAR2014:20:45:00
25MAR2014:21:00:00
25MAR2014:21:15:00
25MAR2014:21:30:00
25MAR2014:21:45:00
08SEP2008:20:15:00
08SEP2008:20:30:00
08SEP2008:20:45:00
08SEP2008:21:00:00
08SEP2008:21:15:00
08SEP2008:21:30:00
08SEP2008:21:45:00
04MAR2005:11:15:00
04MAR2005:11:30:00
04MAR2005:11:45:00
04MAR2005:12:00:00
04MAR2005:12:15:00
04MAR2005:12:30:00
04MAR2005:12:45:00
09MAR2005:11:15:00
09MAR2005:11:30:00
09MAR2005:11:45:00
09MAR2005:12:00:00
09MAR2005:12:15:00
09MAR2005:12:30:00
09MAR2005:12:45:00
19DEC2016:15:15:00
19DEC2016:15:30:00
19DEC2016:15:45:00
19DEC2016:16:00:00
19DEC2016:16:15:00
19DEC2016:16:30:00
19DEC2016:16:45:00
24FEB2006:11:15:00
24FEB2006:11:30:00
24FEB2006:11:45:00
24FEB2006:12:00:00
24FEB2006:12:15:00
24FEB2006:12:30:00
24FEB2006:12:45:00
04MAY2017:08:15:00
04MAY2017:08:30:00
04MAY2017:08:45:00
04MAY2017:09:00:00
04MAY2017:09:15:00
04MAY2017:09:30:00
04MAY2017:09:45:00
17APR2013:03:15:00
17APR2013:03:30:00
17APR2013:03:45:00
17APR2013:04:00:00
17APR2013:04:15:00
17APR2013:04:30:00
17APR2013:04:45:00
04MAR2010:05:15:00
04MAR2010:05:30:00
04MAR2010:05:45:00
04MAR2010:06:00:00
04MAR2010:06:15:00
04MAR2010:06:30:00
04MAR2010:06:45:00
26JUN2013:12:15:00
26JUN2013:12:30:00
26JUN2013:12:45:00
26JUN2013:13:00:00
26JUN2013:13:15:00
26JUN2013:13:30:00
26JUN2013:13:45:00
16FEB2017:00:15:00
16FEB2017:00:30:00
16FEB2017:00:45:00
16FEB2017:01:00:00
16FEB2017:01:15:00
16FEB2017:01:30:00
16FEB2017:01:45:00
29MAY2008:08:15:00
29MAY2008:08:30:00
29MAY2008:08:45:00
29MAY2008:09:00:00
29MAY2008:09:15:00
29MAY2008:09:30:00
29MAY2008:09:45:00
30NOV2010:09:15:00
30NOV2010:09:30:00
30NOV2010:09:45:00
30NOV2010:10:00:00
30NOV2010:10:15:00
30NOV2010:10:30:00
30NOV2010:10:45:00
06OCT2010:04:15:00
06OCT2010:04:30:00
06OCT2010:04:45:00
06OCT2010:05:00:00
06OCT2010:05:15:00
06OCT2010:05:30:00
06OCT2010:05:45:00
03SEP2012:01:15:00
03SEP2012:01:30:00
03SEP2012:01:45:00
03SEP2012:02:00:00
03SEP2012:02:15:00
03SEP2012:02:30:00
03SEP2012:02:45:00
08DEC2009:10:15:00
08DEC2009:10:30:00
08DEC2009:10:45:00
08DEC2009:11:00:00
08DEC2009:11:15:00
08DEC2009:11:30:00
08DEC2009:11:45:00
04MAY2017:00:15:00
04MAY2017:00:30:00
04MAY2017:00:45:00
04MAY2017:01:00:00
04MAY2017:01:15:00
04MAY2017:01:30:00
04MAY2017:01:45:00
27FEB2012:06:15:00
27FEB2012:06:30:00
27FEB2012:06:45:00
27FEB2012:07:00:00
27FEB2012:07:15:00
27FEB2012:07:30:00
27FEB2012:07:45:00
02SEP2004:17:15:00
02SEP2004:17:30:00
02SEP2004:17:45:00
02SEP2004:18:00:00
02SEP2004:18:15:00
02SEP2004:18:30:00
02SEP2004:18:45:00
26AUG2013:04:15:00
26AUG2013:04:30:00
26AUG2013:04:45:00
26AUG2013:05:00:00
26AUG2013:05:15:00
26AUG2013:05:30:00
26AUG2013:05:45:00
10SEP2007:17:15:00
10SEP2007:17:30:00
10SEP2007:17:45:00
10SEP2007:18:00:00
10SEP2007:18:15:00
10SEP2007:18:30:00
10SEP2007:18:45:00
23APR2007:08:15:00
23APR2007:08:30:00
23APR2007:08:45:00
23APR2007:09:00:00
23APR2007:09:15:00
23APR2007:09:30:00
23APR2007:09:45:00
28AUG2008:09:15:00
28AUG2008:09:30:00
28AUG2008:09:45:00
28AUG2008:10:00:00
28AUG2008:10:15:00
28AUG2008:10:30:00
28AUG2008:10:45:00
24AUG2010:00:15:00
24AUG2010:00:30:00
24AUG2010:00:45:00
24AUG2010:01:00:00
24AUG2010:01:15:00
24AUG2010:01:30:00
24AUG2010:01:45:00
15FEB2013:00:15:00
15FEB2013:00:30:00
15FEB2013:00:45:00
15FEB2013:01:00:00
15FEB2013:01:15:00
15FEB2013:01:30:00
15FEB2013:01:45:00
23OCT2007:14:15:00
23OCT2007:14:30:00
23OCT2007:14:45:00
23OCT2007:15:00:00
23OCT2007:15:15:00
23OCT2007:15:30:00
23OCT2007:15:45:00
27JAN2012:13:15:00
27JAN2012:13:30:00
27JAN2012:13:45:00
27JAN2012:14:00:00
27JAN2012:14:15:00
27JAN2012:14:30:00
27JAN2012:14:45:00
17NOV2003:02:15:00
17NOV2003:02:30:00
17NOV2003:02:45:00
17NOV2003:03:00:00
17NOV2003:03:15:00
17NOV2003:03:30:00
17NOV2003:03:45:00
18JAN2013:16:15:00
18JAN2013:16:30:00
18JAN2013:16:45:00
18JAN2013:17:00:00
18JAN2013:17:15:00
18JAN2013:17:30:00
18JAN2013:17:45:00
29AUG2017:00:15:00
29AUG2017:00:30:00
29AUG2017:00:45:00
29AUG2017:01:00:00
29AUG2017:01:15:00
29AUG2017:01:30:00
29AUG2017:01:45:00
07JUL2006:12:15:00
07JUL2006:12:30:00
07JUL2006:12:45:00
07JUL2006:13:00:00
07JUL2006:13:15:00
07JUL2006:13:30:00
07JUL2006:13:45:00
20APR2017:09:15:00
20APR2017:09:30:00
20APR2017:09:45:00
20APR2017:10:00:00
20APR2017:10:15:00
20APR2017:10:30:00
20APR2017:10:45:00
23JAN2009:08:15:00
23JAN2009:08:30:00
23JAN2009:08:45:00
23JAN2009:09:00:00
23JAN2009:09:15:00
23JAN2009:09:30:00
23JAN2009:09:45:00
17MAY2013:01:15:00
17MAY2013:01:30:00
17MAY2013:01:45:00
17MAY2013:02:00:00
17MAY2013:02:15:00
17MAY2013:02:30:00
17MAY2013:02:45:00
27JUL2015:08:15:00
27JUL2015:08:30:00
27JUL2015:08:45:00
27JUL2015:09:00:00
27JUL2015:09:15:00
27JUL2015:09:30:00
27JUL2015:09:45:00
15JAN2013:12:15:00
15JAN2013:12:30:00
15JAN2013:12:45:00
15JAN2013:13:00:00
15JAN2013:13:15:00
15JAN2013:13:30:00
15JAN2013:13:45:00
29DEC2010:07:15:00
29DEC2010:07:30:00
29DEC2010:07:45:00
29DEC2010:08:00:00
29DEC2010:08:15:00
29DEC2010:08:30:00
29DEC2010:08:45:00
17AUG2012:02:15:00
17AUG2012:02:30:00
17AUG2012:02:45:00
17AUG2012:03:00:00
17AUG2012:03:15:00
17AUG2012:03:30:00
17AUG2012:03:45:00
10FEB2010:23:15:00
10FEB2010:23:30:00
10FEB2010:23:45:00
11FEB2010:00:00:00
11FEB2010:00:15:00
11FEB2010:00:30:00
11FEB2010:00:45:00
17JUN2009:19:15:00
17JUN2009:19:30:00
17JUN2009:19:45:00
17JUN2009:20:00:00
17JUN2009:20:15:00
17JUN2009:20:30:00
17JUN2009:20:45:00
;

data test2;
  infile datalines dlm='|' dsd truncover;
  length date dop 8;
  informat date datetime20.;
  format date datetime20.;
  input date  dop;
datalines;
04FEB2014:18:15:00|12.926325836
04FEB2014:18:30:00|10.467683104
04FEB2014:18:45:00|14.553949004
04FEB2014:19:00:00|16.753267504
04FEB2014:19:15:00|15.432721685
04FEB2014:19:30:00|13.079502188
04FEB2014:19:45:00|3.2931986085
01SEP2005:20:15:00|5.8031838462
01SEP2005:20:30:00|5.7267842854
01SEP2005:20:45:00|12.716574661
01SEP2005:21:00:00|11.196986705
01SEP2005:21:15:00|8.1857230854
01SEP2005:21:30:00|6.0963572731
01SEP2005:21:45:00|3.893083084
26SEP2008:18:15:00|4.527732406
26SEP2008:18:30:00|5.998708063
26SEP2008:18:45:00|8.0575315924
26SEP2008:19:00:00|14.055064085
26SEP2008:19:15:00|16.027621714
26SEP2008:19:30:00|17.753654608
26SEP2008:19:45:00|14.169359164
18JAN2017:10:15:00|4.7333510969
18JAN2017:10:30:00|7.1205629521
18JAN2017:10:45:00|9.4999079941
18JAN2017:11:00:00|10.529906839
18JAN2017:11:15:00|8.2806338208
18JAN2017:11:30:00|4.8167229459
18JAN2017:11:45:00|1.9273408359
04SEP2009:18:15:00|4.5940267604
04SEP2009:18:30:00|6.6953024902
04SEP2009:18:45:00|11.239162139
04SEP2009:19:00:00|14.505392395
04SEP2009:19:15:00|13.862565408
04SEP2009:19:30:00|12.493184428
04SEP2009:19:45:00|6.2268856111
03JUN2004:03:15:00|3.8785345451
03JUN2004:03:30:00|5.4163551218
03JUN2004:03:45:00|7.5926948085
03JUN2004:04:00:00|8.0540223698
03JUN2004:04:15:00|6.0224157279
03JUN2004:04:30:00|5.2194375376
03JUN2004:04:45:00|5.7988948043
15MAR2013:17:15:00|4.8380905422
15MAR2013:17:30:00|4.5434509521
15MAR2013:17:45:00|6.6362965781
15MAR2013:18:00:00|7.3008831926
15MAR2013:18:15:00|4.9033131208
15MAR2013:18:30:00|2.8272261643
15MAR2013:18:45:00|1.7922556066
07MAR2014:05:15:00|4.3285095974
07MAR2014:05:30:00|7.2981733036
07MAR2014:05:45:00|10.707264213
07MAR2014:06:00:00|17.432833375
07MAR2014:06:15:00|18.405391004
07MAR2014:06:30:00|17.613853233
07MAR2014:06:45:00|11.788406783
03FEB2015:06:15:00|5.8983106056
03FEB2015:06:30:00|7.6946683748
03FEB2015:06:45:00|9.0681623507
03FEB2015:07:00:00|10.31011877
03FEB2015:07:15:00|9.1303921355
03FEB2015:07:30:00|6.6998887191
03FEB2015:07:45:00|2.2032736082
01JUN2011:09:15:00|5.0419964602
01JUN2011:09:30:00|7.1904291959
01JUN2011:09:45:00|10.079318085
01JUN2011:10:00:00|11.396980339
01JUN2011:10:15:00|9.1644097627
01JUN2011:10:30:00|6.3602747992
01JUN2011:10:45:00|1.3610847793
31JUL2007:18:15:00|8.9031838462
31JUL2007:18:30:00|7.4895724302
31JUL2007:18:45:00|8.7892431218
31JUL2007:19:00:00|10.969629911
31JUL2007:19:15:00|8.9421875405
31JUL2007:19:30:00|6.3173247523
31JUL2007:19:45:00|2.6781004566
09MAR2004:18:15:00|5.0746660731
09MAR2004:18:30:00|6.8610359729
09MAR2004:18:45:00|8.9035891644
09MAR2004:19:00:00|9.012112181
09MAR2004:19:15:00|6.68966981
09MAR2004:19:30:00|5.0985360313
09MAR2004:19:45:00|2.6479797102
05APR2005:21:15:00|4.7807577929
05APR2005:21:30:00|6.7413551218
05APR2005:21:45:00|10.303855122
05APR2005:22:00:00|11.925211672
05APR2005:22:15:00|10.878901377
05APR2005:22:30:00|8.5289373626
05APR2005:22:45:00|2.4088597301
04NOV2010:01:15:00|4.5076049258
04NOV2010:01:30:00|6.3068313123
04NOV2010:01:45:00|8.4496884551
04NOV2010:02:00:00|8.3129134936
04NOV2010:02:15:00|5.8335812852
04NOV2010:02:30:00|4.8659114019
04NOV2010:02:45:00|4.2581379748
15FEB2007:16:15:00|5.7227059268
15FEB2007:16:30:00|7.0889127508
15FEB2007:16:45:00|12.577893583
15FEB2007:17:00:00|19.511510253
15FEB2007:17:15:00|21.950734549
15FEB2007:17:30:00|21.791520011
15FEB2007:17:45:00|14.928089323
25MAR2015:08:15:00|3.5722508056
25MAR2015:08:30:00|4.5685834424
25MAR2015:08:45:00|7.0054220812
25MAR2015:09:00:00|8.1710003669
25MAR2015:09:15:00|6.1109265219
25MAR2015:09:30:00|3.4338843105
25MAR2015:09:45:00|1.7153971336
22MAY2006:02:15:00|10.695233399
22MAY2006:02:30:00|7.527913343
22MAY2006:02:45:00|7.0180340894
22MAY2006:03:00:00|7.4044216108
22MAY2006:03:15:00|4.4667706778
22MAY2006:03:30:00|2.0747775775
22MAY2006:03:45:00|2.9990334972
28MAY2012:02:15:00|4.7889127508
28MAY2012:02:30:00|7.6163551218
28MAY2012:02:45:00|11.616355122
28MAY2012:03:00:00|13.767669127
28MAY2012:03:15:00|12.534344403
28MAY2012:03:30:00|10.226763852
28MAY2012:03:45:00|2.7611217003
08SEP2008:20:15:00|6.9714238755
08SEP2008:20:30:00|5.0951340841
08SEP2008:20:45:00|7.0496884551
08SEP2008:21:00:00|13.192357184
08SEP2008:21:15:00|14.528551177
08SEP2008:21:30:00|15.127922497
08SEP2008:21:45:00|11.398214282
04JUN2014:18:15:00|10.404757209
04JUN2014:18:30:00|6.4951340841
04JUN2014:18:45:00|6.6781320107
04JUN2014:19:00:00|15.394629911
04JUN2014:19:15:00|15.74218754
04JUN2014:19:30:00|14.771861891
04JUN2014:19:45:00|12.050631706
15MAY2017:21:15:00|6.5491909427
15MAY2017:21:30:00|8.1312804949
15MAY2017:21:45:00|8.7730715397
15MAY2017:22:00:00|9.3788338916
15MAY2017:22:15:00|6.9347248539
15MAY2017:22:30:00|5.6024059325
15MAY2017:22:45:00|2.2636291795
04MAR2005:11:15:00|4.01006798
04MAR2005:11:30:00|5.1299627527
04MAR2005:11:45:00|6.685320639
04MAR2005:12:00:00|9.4753770379
04MAR2005:12:15:00|7.9306932876
04MAR2005:12:30:00|5.904045799
04MAR2005:12:45:00|2.5175825745
02MAR2010:18:15:00|3.502205214
02MAR2010:18:30:00|6.6446570086
02MAR2010:18:45:00|12.795600405
02MAR2010:19:00:00|15.864808109
02MAR2010:19:15:00|14.837365738
02MAR2010:19:30:00|12.164943652
02MAR2010:19:45:00|3.2867428169
07APR2006:03:15:00|5.3517607699
07APR2006:03:30:00|8.5709005763
07APR2006:03:45:00|11.752718758
07APR2006:04:00:00|11.889664505
07APR2006:04:15:00|10.358779364
07APR2006:04:30:00|8.5102945157
07APR2006:04:45:00|6.7527187582
06APR2005:12:15:00|5.0790334972
06APR2005:12:30:00|7.0790880411
06APR2005:12:45:00|10.190889283
06APR2005:13:00:00|10.676048131
06APR2005:13:15:00|7.8819390933
06APR2005:13:30:00|6.1361749908
06APR2005:13:45:00|3.9790334972
30JAN2007:17:15:00|8.2981733036
30JAN2007:17:30:00|9.0557490612
30JAN2007:17:45:00|8.4199675357
30JAN2007:18:00:00|11.14795159
30JAN2007:18:15:00|11.607688706
30JAN2007:18:30:00|11.648318767
30JAN2007:18:45:00|8.4221064167
16FEB2017:00:15:00|6.7830217885
16FEB2017:00:30:00|9.1163551218
16FEB2017:00:45:00|12.752718758
16FEB2017:01:00:00|14.196902639
16FEB2017:01:15:00|13.654308753
16FEB2017:01:30:00|11.574892194
16FEB2017:01:45:00|4.7012445855
29MAY2008:08:15:00|8.6789996331
29MAY2008:08:30:00|5.5084328235
29MAY2008:08:45:00|6.4847814266
29MAY2008:09:00:00|10.082159076
29MAY2008:09:15:00|10.157030418
29MAY2008:09:30:00|10.165801285
29MAY2008:09:45:00|7.8202922045
06OCT2008:13:15:00|4.0619217163
06OCT2008:13:30:00|6.2940200964
06OCT2008:13:45:00|8.0554414162
06OCT2008:14:00:00|8.53930815
06OCT2008:14:15:00|6.920260531
06OCT2008:14:30:00|6.3447815177
06OCT2008:14:45:00|6.8147678202
06OCT2010:04:15:00|4.1761514473
06OCT2010:04:30:00|5.1783989174
06OCT2010:04:45:00|7.2172468987
06OCT2010:05:00:00|13.006126262
06OCT2010:05:15:00|13.46982942
06OCT2010:05:30:00|14.00496794
06OCT2010:05:45:00|12.028686956
11JAN2008:05:15:00|6.7227059268
11JAN2008:05:30:00|4.8184305462
11JAN2008:05:45:00|6.9934977377
11JAN2008:06:00:00|10.449879848
11JAN2008:06:15:00|10.612887532
11JAN2008:06:30:00|9.1799876474
11JAN2008:06:45:00|7.7012445855
03SEP2012:01:15:00|5.4990334972
03SEP2012:01:30:00|7.2663551218
03SEP2012:01:45:00|7.9954220812
03SEP2012:02:00:00|9.9852709371
03SEP2012:02:15:00|9.4962901046
03SEP2012:02:30:00|6.9321539059
03SEP2012:02:45:00|4.4064009366
05JUN2013:01:15:00|17.974454179
05JUN2013:01:30:00|12.716346205
05JUN2013:01:45:00|9.9758512448
05JUN2013:02:00:00|10.398392843
05JUN2013:02:15:00|9.3839831031
05JUN2013:02:30:00|7.2440070527
05JUN2013:02:45:00|5.3756317058
10APR2007:08:15:00|4.7017269058
10APR2007:08:30:00|5.6618096673
10APR2007:08:45:00|8.9042339097
10APR2007:09:00:00|8.8759115917
10APR2007:09:15:00|7.4759115917
10APR2007:09:30:00|6.2155386313
10APR2007:09:45:00|5.6174950357
30NOV2010:10:15:00|5.6618096673
30NOV2010:10:30:00|8.6108178149
30NOV2010:10:45:00|3.7015904955
30NOV2010:11:00:00|8.6061510402
30NOV2010:11:15:00|2.4724274822
30NOV2010:11:30:00|7.9132505479
30NOV2010:11:45:00|3.3118030883
30JAN2017:12:15:00|8.6108178149
30JAN2017:12:30:00|6.1254371144
30JAN2017:12:45:00|8.7527187582
30JAN2017:13:00:00|11.378198355
30JAN2017:13:15:00|9.5116755237
30JAN2017:13:30:00|8.2476036379
30JAN2017:13:45:00|3.7015904955
27FEB2012:06:15:00|5.9361763543
27FEB2012:06:30:00|6.7694163463
27FEB2012:06:45:00|8.6061510402
27FEB2012:07:00:00|9.9900380747
27FEB2012:07:15:00|8.5340242752
27FEB2012:07:30:00|6.6188006664
27FEB2012:07:45:00|2.4724274822
26AUG2013:04:15:00|4.3948318591
26AUG2013:04:30:00|5.462508968
26AUG2013:04:45:00|6.5394320449
26AUG2013:05:00:00|7.9132505479
26AUG2013:05:15:00|5.7109176157
26AUG2013:05:30:00|4.730512842
26AUG2013:05:45:00|3.8139732562
03SEP2004:10:15:00|6.0658622216
03SEP2004:10:30:00|5.7151690477
03SEP2004:10:45:00|10.783021788
03SEP2004:11:00:00|11.809115141
03SEP2004:11:15:00|10.180302314
03SEP2004:11:30:00|7.9409789672
03SEP2004:11:45:00|4.6012036067
22JAN2009:07:15:00|6.6254121668
22JAN2009:07:30:00|7.3645724302
22JAN2009:07:45:00|9.3508512448
22JAN2009:08:00:00|15.934611783
22JAN2009:08:15:00|19.779437649
22JAN2009:08:30:00|21.702325649
22JAN2009:08:45:00|22.580032464
28AUG2008:09:15:00|4.4957001639
28AUG2008:09:30:00|7.5330217885
28AUG2008:09:45:00|8.7830217885
28AUG2008:10:00:00|13.163569305
28AUG2008:10:15:00|14.317945116
28AUG2008:10:30:00|14.541558861
28AUG2008:10:45:00|10.334577919
24AUG2010:00:15:00|8.3703233758
24AUG2010:00:30:00|9.1004821059
24AUG2010:00:45:00|9.1957202012
24AUG2010:01:00:00|11.183915626
24AUG2010:01:15:00|11.489806588
24AUG2010:01:30:00|10.402814272
24AUG2010:01:45:00|5.8422983725
15FEB2013:00:15:00|5.1303155485
15FEB2013:00:30:00|7.1419961474
15FEB2013:00:45:00|9.7830217885
15FEB2013:01:00:00|9.8086628141
15FEB2013:01:15:00|8.8086628141
15FEB2013:01:30:00|8.6078081133
15FEB2013:01:45:00|7.6163551218
23JAN2006:08:15:00|6.3682896955
23JAN2006:08:30:00|7.1370162788
23JAN2006:08:45:00|8.0604634035
23JAN2006:09:00:00|10.80673837
23JAN2006:09:15:00|11.032382419
23JAN2006:09:30:00|11.219928415
23JAN2006:09:45:00|8.1518618694
29FEB2008:10:15:00|5.3424122603
29FEB2008:10:30:00|6.8420188386
29FEB2008:10:45:00|10.488036538
29FEB2008:11:00:00|11.311008297
29FEB2008:11:15:00|9.7381113806
29FEB2008:11:30:00|7.4373142934
29FEB2008:11:45:00|2.4340878831
23OCT2007:14:15:00|5.3851559462
23OCT2007:14:30:00|7.4020694075
23OCT2007:14:45:00|7.9968407958
23OCT2007:15:00:00|12.867354973
23OCT2007:15:15:00|15.283924235
23OCT2007:15:30:00|16.872502147
23OCT2007:15:45:00|15.828695566
27JAN2012:13:15:00|7.8782598837
27JAN2012:13:30:00|9.2750852805
27JAN2012:13:45:00|9.4458203877
27JAN2012:14:00:00|9.2511778354
27JAN2012:14:15:00|7.8226436936
27JAN2012:14:30:00|5.9807507798
27JAN2012:14:45:00|3.0507568782
03DEC2012:07:15:00|8.9496884551
03DEC2012:07:30:00|9.6719106774
03DEC2012:07:45:00|8.838577344
03DEC2012:08:00:00|8.4574855733
03DEC2012:08:15:00|6.1633705618
03DEC2012:08:30:00|5.179336919
03DEC2012:08:45:00|3.7870466696
05MAR2009:14:15:00|4.6307576351
05MAR2009:14:30:00|7.737044777
05MAR2009:14:45:00|10.357734432
05MAR2009:15:00:00|11.347790831
05MAR2009:15:15:00|9.9712815006
05MAR2009:15:30:00|8.5864788963
05MAR2009:15:45:00|4.2678370098
01DEC2004:07:15:00|7.3317397372
01DEC2004:07:30:00|8.6932781987
01DEC2004:07:45:00|10.000970506
01DEC2004:08:00:00|10.448960105
01DEC2004:08:15:00|8.1336752645
01DEC2004:08:30:00|6.314888829
01DEC2004:08:45:00|1.8031984651
17NOV2003:02:15:00|5.255863304
17NOV2003:02:30:00|6.5991137425
17NOV2003:02:45:00|10.897964317
17NOV2003:03:00:00|11.999955174
17NOV2003:03:15:00|10.483151101
17NOV2003:03:30:00|8.5484576356
17NOV2003:03:45:00|1.875212585
29AUG2017:00:15:00|3.529699275
29AUG2017:00:30:00|6.7830217885
29AUG2017:00:45:00|9.7830217885
29AUG2017:01:00:00|14.080741023
29AUG2017:01:15:00|14.682928281
29AUG2017:01:30:00|13.642232261
29AUG2017:01:45:00|7.7938371781
07JUL2006:12:15:00|3.9513739227
07JUL2006:12:30:00|6.2440146963
07JUL2006:12:45:00|9.2014615048
07JUL2006:13:00:00|10.492876562
07JUL2006:13:15:00|8.5765453025
07JUL2006:13:30:00|6.3437942059
07JUL2006:13:45:00|2.1416834139
20JUN2013:00:15:00|7.6714238755
20JUN2013:00:30:00|5.0475150365
20JUN2013:00:45:00|7.8368621694
20JUN2013:01:00:00|8.9728112438
20JUN2013:01:15:00|7.1754684223
20JUN2013:01:30:00|7.1368805734
20JUN2013:01:45:00|5.737044777
01MAY2017:02:15:00|5.3363551218
01MAY2017:02:30:00|11.716355122
01MAY2017:02:45:00|22.616355122
01MAY2017:03:00:00|24.044781427
01MAY2017:03:15:00|22.644611783
01MAY2017:03:30:00|16.927619467
01MAY2017:03:45:00|2.6380296055
15JAN2013:12:15:00|5.0790334972
15JAN2013:12:30:00|6.9496884551
15JAN2013:12:45:00|8.0830217885
15JAN2013:13:00:00|9.5530848993
15JAN2013:13:15:00|8.1387812144
15JAN2013:13:30:00|6.7886502121
15JAN2013:13:45:00|2.8689891889
22JUL2013:02:15:00|6.4830217885
22JUL2013:02:30:00|8.5996884551
22JUL2013:02:45:00|10.916355122
22JUL2013:03:00:00|11.340463245
22JUL2013:03:15:00|9.6880208738
22JUL2013:03:30:00|7.9548659159
22JUL2013:03:45:00|3.7404861135
15APR2013:12:15:00|7.3689547397
15APR2013:12:30:00|6.1259982817
15APR2013:12:45:00|8.999119665
15APR2013:13:00:00|10.242102905
15APR2013:13:15:00|7.7380980343
15APR2013:13:30:00|5.4546291936
15APR2013:13:45:00|3.0246761632
29DEC2010:07:15:00|6.4809476851
29DEC2010:07:30:00|4.0614714424
29DEC2010:07:45:00|7.1601061019
29DEC2010:08:00:00|8.0381552079
29DEC2010:08:15:00|5.9081334208
29DEC2010:08:30:00|3.9020543935
29DEC2010:08:45:00|3.6534782272
13JAN2009:07:15:00|7.5991137425
13JAN2009:07:30:00|9.7025620184
13JAN2009:07:45:00|10.40945857
13JAN2009:08:00:00|10.100664394
13JAN2009:08:15:00|7.9596106072
13JAN2009:08:30:00|5.625555571
13JAN2009:08:45:00|3.0926449132
;

RW9 · Posted 09-24-2018 10:20 AM

That post is too long to read. From what I can gather you want to remove certain data, and test it first. What I would suggest is you join the smaller dataset onto the bigger dataset:

from big_dataset a
left join small_dataset b
on    ...

You can select the b. variables with a different name, e.g. b.abc=b_abc. This will then show you all the data and where the matching is so you can see if the matching is correct. You would then delete from the data where b_abc is present.

SuryaKiran · Posted 09-24-2018 10:26 AM

Take the date part only instead of the datetime. Sub-query might better work for you.

proc sql;
create table test3 as
select * from test2
where datepart(date) not in (select distinct datepart(date) from test1);
quit;

Thanks,
Suryakiran

makset · Posted 09-24-2018 11:16 AM

ok

I understand your solution but!!

you can explain why the test3 dataset has 231 observations because I think you should have 245! why?
because from the test (test2) dataset these observations should not be removed:

30NOV2010:10:15:00|5.6618096673
30NOV2010:10:30:00|8.6108178149
30NOV2010:10:45:00|3.7015904955
30NOV2010:11:00:00|8.6061510402
30NOV2010:11:15:00|2.4724274822
30NOV2010:11:30:00|7.9132505479
30NOV2010:11:45:00|3.3118030883

SuryaKiran · Posted 09-24-2018 03:20 PM

Not sure what logic your using here, both the test1 and test2 datasets you provided have records with date 30NOV2010 with count 7. Do a full join to find the records your looking for.

proc sql;
create table test as
select a.date as table1_date,b.date as table2_date,count_1,count_2
from (select datepart(date) format=date9. as date,count(*) as count_1
		from test1
		group by 1) a
full  join	(select datepart(date) format=date9. as date,count(*) as count_2
		from test2
		group by 1) b
on a.date=b.date
;
quit;

You need to add a where clause to find the records you need depending on your logic here.

I guess your logic might be something like this:

where (a.date is null and count_1 is null and count_2=7)
 	or (a.date=b.date and count_1<count_2 and count_2=7)

Thanks,
Suryakiran

makset · Posted 09-24-2018 11:45 AM

ok I have a solution but I used your idea

data test4;
set  test1;
datetest = date + lag6(date);
n = _n_;
run;

			proc sort data =  test4;
			key n / descending; 
			run;

data test4 (drop = maxveh0);
set  test4;
retain maxveh0;
if mod((_n_ + 6), 7) = 0 then do;
maxveh0 = datetest;
end;
datetest = maxveh0;
run;

			proc sort data =  test4 out = test4 (drop = n);
			key n / ascending;
			run;


data test5;
set  test2;
datetest = date + lag6(date);
n = _n_;
run;


			proc sort data =  test5;
			key n / descending; 
			run;

data test5 (drop = maxveh0);
set  test5;
retain maxveh0;
if mod((_n_ + 6), 7) = 0 then do;
maxveh0 = datetest;
end;
datetest = maxveh0;
run;

			proc sort data =  test5 out = test5 (drop = n);
			key n / ascending;
			run;


proc sql;
create table test6 (drop = datetest) as
select * from test5
where datetest not in (select distinct datetest from test4);
quit;

until I'm proud of myself haha (joke)

novinosrin · Posted 09-24-2018 12:08 PM

data _null_;
   dcl hash H (multidata:'y') ;
   h.definekey  ('_date') ;
   h.definedata ('_date','date') ;
   h.definedone () ;
do until(lr);
set test1 end=lr;
_date=datepart(date);
rc=h.add();
end;
lr=0;
do until(lr);
set test2 end=lr;
_date=datepart(date);
if lag(_date) ne _date then n=1;
else n+1;
if n=7 and h.check()=0 then h.remove();
end;
if lr then h.output(dataset:'want');
stop;
run;

novinosrin · Posted 09-24-2018 10:53 AM

Are you exclusively looking for sql solution or either(sql/datastep)?

PGStats · Posted 09-24-2018 06:07 PM

So what would be the result set when sequences of 7 overlap:

test1
27JAN2012:13:15:00 
27JAN2012:13:30:00
27JAN2012:13:45:00
27JAN2012:14:00:00
27JAN2012:14:15:00
27JAN2012:14:30:00
27JAN2012:14:45:00

test2
27JAN2012:13:15:00 1
27JAN2012:13:30:00 2
27JAN2012:13:45:00 3
27JAN2012:14:00:00 4
27JAN2012:14:15:00 5
27JAN2012:14:30:00 6
27JAN2012:14:45:00 7
27JAN2012:15:00:00 8
27JAN2012:15:15:00 9
27JAN2012:15:30:00 10
27JAN2012:15:45:00 11

S

PG

proc sql except - customize