cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
makset
Obsidian | Level 7 makset
Obsidian | Level 7

proc sql except - customize

Posted 09-24-2018 10:02 AM (2324 views)

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) test.jpg

 

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
;

 

 

0 Likes
Reply
8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Re: proc sql except - customize

Posted 09-24-2018 10:20 AM (2312 views)  |   In reply to makset

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.

0 Likes
Reply
SuryaKiran
Meteorite | Level 14 SuryaKiran
Meteorite | Level 14

Re: proc sql except - customize

Posted 09-24-2018 10:26 AM (2307 views)  |   In reply to makset

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
0 Likes
Reply
makset
Obsidian | Level 7 makset
Obsidian | Level 7

Re: proc sql except - customize

Posted 09-24-2018 11:16 AM (2291 views)  |   In reply to SuryaKiran

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

 

 

0 Likes
Reply
SuryaKiran
Meteorite | Level 14 SuryaKiran
Meteorite | Level 14

Re: proc sql except - customize

Posted 09-24-2018 03:20 PM (2270 views)  |   In reply to makset

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
0 Likes
Reply
makset
Obsidian | Level 7 makset
Obsidian | Level 7

Re: proc sql except - customize

Posted 09-24-2018 11:45 AM (2284 views)  |   In reply to SuryaKiran

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) 

0 Likes
Reply
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: proc sql except - customize

Posted 09-24-2018 12:08 PM (2277 views)  |   In reply to makset 
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;
0 Likes
Reply
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: proc sql except - customize

Posted 09-24-2018 10:53 AM (2298 views)  |   In reply to makset

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

0 Likes
Reply
PGStats
Opal | Level 21 PGStats
Opal | Level 21

Re: proc sql except - customize

Posted 09-24-2018 06:07 PM (2265 views)  |   In reply to makset

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
0 Likes
Reply

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • ‎09-24-2018 10:02 AM
  • 2325 views
  • 0 likes
  • 5 in conversation