Thank you @Kurt_Bremser - it seems to work perfectly! ... View more

Thanks @PeterClemmensen. Unfortunately, it won't work for me. I'm not sure how to incorporate the MMSS format in the code. Can you elaborate? ... View more

Thanks to both of you!   If I want to calculate the mean difference I have tried the following code:   PROC MEANS data=have N Mean median min max Q1 Q3 nmiss maxdec=2; VAR Difference; Run; It returns the mean, meadian, min, max, Q1, and Q3 in seconds. Can it be displayed in min:sec instead?  ... View more

Perfect, thank you a lot @PeterClemmensen! All best MM ... View more

Thank you so much @FreelanceReinh!    It was indeed unbalanced parentheses that caused the problem.  It seems like that the programme runs without any errors! Wuhu! 🙂   Regarding the test data, you are of course correct. It would help a lot. If I encounter other problems it would be reasonable to work on a solution with a test dataset.    I will make a quality control of the output data, to ensure there is no errors in either the output itself nor the original databases. If any problems arises I might contact you once again. I would love to send you a bottle of wine or something. You have done a tremendous job helping me out! Thanks once again.   Have a nice weekend and summertime.    All best MM     ... View more

Hi @FreelanceReinh,   Sorry! Several mistakes have been made and they are all mine. The open/ close variables was indeed numeric. However, I had trouble with the dataset, and I tried to export/ import it, which changed the variables to character (without me noticing). Sorry!    However, whether I run it with or without the new modification (with the input function), I just get another error code:    ERROR 79-322: Expecting a ).    Affected code: !! input(b.open,time8.)<=timepart(a.eventtime)<=input(b.close,time8.) as d0_360   The same errormessage is repeated three times (from each last line in all three new variables).     I do understand if you are feeling a bit tired, and it is totally okay if enough is enough! I'm also feeling a bit tired and thinking about other ways to do this. I would love to share a part of my original data but it is containing sensitive information that I'm not allowed to share.    ... View more

Impressive @FreelanceReinh!   I'm sorry for continuing to drop (stupid) questions and problems in your direction.  When I run the full code from your last post:   proc sql; create table Data_komplet_Geo as select a.*, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0<=geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=0.360 & (upcase(left(b.'247'n)) eqt 'Y' | upcase(left(put(datepart(a.eventtime),downame.)))=upcase(left(b.Type)) & b.open<=timepart(a.eventtime)<=b.close)) as d0_360, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0.360<geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=1.08 & (upcase(left(b.'247'n)) eqt 'Y' | upcase(left(put(datepart(a.eventtime),downame.)))=upcase(left(b.Type)) & b.open<=timepart(a.eventtime)<=b.close)) as d360_1080, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 1.08<geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=1.8 & (upcase(left(b.'247'n)) eqt 'Y' | upcase(left(put(datepart(a.eventtime),downame.)))=upcase(left(b.Type)) & b.open<=timepart(a.eventtime)<=b.close)) as d1080_1800 from Final_database a; quit; I get the new following error messages:    ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using greater than or equal (>=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using greater than or equal (>=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using greater than or equal (>=) has components that are of different data types.   Is that because of the format or type of my different time variables used?  Eventtime: type = num, format = DATETIME19. (shown in my database as: "14MAY2018:13:15:10" from Final_database) Createdtime: type = num, format = DATETIME18. (shown in my database as: "11JAN13:09:52:00" from Data_komplet) Open: type = char, format = $CHAR8. (shown in my database as: "8:30:00" from Data_komplet)  Close: type = char, format = $CHAR8. (shown in my database as: "16:00:00" from Data_komplet)   Thanks once again! 🙂        ... View more

Thank you, @FreelanceReinh.   The programme now looks like:   proc sql; create table Data_komplet_Geo as select a.*, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0<=geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=0.360) & (upcase(left(b.'247'n)) eqt Y | upcase(put(datepart(a.eventtime),downame.-l))=upcase(left(b.Type)) & b.'open'n<=timepart(a.eventtime)<=b.'close'n) as d0_360, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0.360<geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=1.08) & (upcase(left(b.'247'n)) eqt Y | upcase(put(datepart(a.eventtime),downame.-l))=upcase(left(b.Type)) & b.'open'n<=timepart(a.eventtime)<=b.'close'n) as d360_1080, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 1.08<geodist(input(incident_northing,32.), input(incident_easting,32.), b.latitude, b.longitude)<=1.8) & (upcase(left(b.'247'n)) eqt Y | upcase(put(datepart(a.eventtime),downame.-l))=upcase(left(b.Type)) & b.'open'n<=timepart(a.eventtime)<=b.'close'n) as d1080_1800 from Final_database a; quit; I do however get a number of errors: ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Unresolved reference to table/correlation name b. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: The following columns were not found in the contributing tables: Y.   The affected code is: "from Final_database a;"   What am I not doing right?    Thanks once again.... You do deserve a bottle of wine or something! 🙂   All best MM ... View more

Hi @FreelanceReinh    Thank you for always being so helpful and kind!    I made a mistake with the createdtime and eventtime. It should only (as in the first programme) count eventtimes that took places after createdtime - so I left the first line unchanged (as in the old programme).    I have checked my variables: 1) The variable "247" (which is the correct name of the variable) is a yes/no character variable (in the format "$CHAR3.).  2) The "Type" variable is also a character variable (in the format $CHAR9.). The values in the "Type" variable is the seven different weekdays (e.g. Monday, tuesday etc.).  3) The variables "Open" and "Close" are both numeric variables with the format TIME8. and the informat TIME11.    Does it require any adjustments?    Thanks again! Have a nice day 🙂   All best MM ... View more

Hi @FreelanceReinh    I hope you are well and that I might may request some further help from you.  You helped me generate this programme:     proc sql; create table Data_komplet_Geo as select a.*, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0<=geodist(input(event_northing,32.), input(event_easting,32.), b.latitude, b.longitude)<=0.360) as d0_360, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 0.360<geodist(input(event_northing,32.), input(event_easting,32.), b.latitude, b.longitude)<=1.08) as d360_1080, (select count(*) from Data_komplet b where .z<intnx('dtyear',a.eventtime,0)<=b.createdtime<=a.eventtime & 1.08<geodist(input(event_northing,32.), input(event_easting,32.), b.latitude, b.longitude)<=1.8) as d1080_1800 from Final_database a; quit;     It worked well, but unfortunately I would like some more. You might remember my two datasets "Final_database" and the three datasets I joined to "Data_komplet". They looked like:   Final_database:   ID     Longitude          Lattitude           Eventtime 1     12.21236548     55.14578965     15FEB201713:15:10 2     12.52319752     55.21460198     19FEB201717:12:10 3     12.41219754     56.41301547     21APR201711:03:11 4     12.14016597     55.49783265     05JUN201808:05:44 5     12.13497326     55.65794325     21DEC201815:59:44 6     12.52319632     55.21446125     26DEC201806:30:12 7     12.41659721     56.41302146     21APR201922:12:42 8     12.14316592     55.49789875     05JUN201902:22:23 9     12.13478134     55.65795469     21DEC2019   Data_komplet:   Createdtime                            Longitude          Lattitude 01FEB1712:00:00     12.62315456     55.46532659 21FEB1708:00:00     12.65312348     55.64325689 05MAR1709:00:00    12.54693265     55.21302356 12JUN1712:00:00     12.06837264     55.21363221   My 'Data_komplet' database have changed a bit to:   Var    Createdtime             Longitude          Lattitude           Type                       Open           Close          24/7 1      01FEB1712:00:00     12.62315456     55.46532659     Monday                 08:00:00     22:00:00        N 1      01FEB1712:00:00     12.62315456     55.46532659     Tuesday                08:00:00     22:00:00        N 1      01FEB1712:00:00     12.62315456     55.46532659     Wednesday           08:00:00     22:00:00        N 1      01FEB1712:00:00     12.62315456     55.46532659     Thursday               08:00:00     22:00:00        N 1      01FEB1712:00:00     12.62315456     55.46532659     Friday                    08:00:00     22:00:00        N 2      21FEB1708:00:00     12.65312348     55.64325689     Monday                 12:00:00     22:00:00        N 2      21FEB1708:00:00     12.65312348     55.64325689     Tuesday                 08:00:00     22:00:00       N 2      21FEB1708:00:00     12.65312348     55.64325689     Wednesday           10:00:00     22:00:00        N 2      21FEB1708:00:00     12.65312348     55.64325689     Thursday               08:00:00     22:00:00        N 3      05MAR1709:00:00    12.54693265     55.21302356     .                               .                 .                    Y 4      12JUN1712:00:00     12.06837264     55.21363221     .                               .                 .                    Y   So... The objective is the same: to count how many observations from 'Data_komplet' that lies within 0-360, 360-1080 and 1080-1800 meters from the observations in 'Final_database'. But it should only count those where eventtime <= createdtime. Further, it should only count those observations from 'Data_komplet' that are either 24/7 (in the first 'Data_komplet' where you helped me, all observations where 24/7) or those where the day in 'Type' matches the weekday of 'Eventtime' and where the time (from 'eventtime') is within the openinghours (between 'Open' and 'Close').    Phew. A bit complicated to explain! Hope that it is clear and that you have a suggestion - if it is possible!    All best, MM       ... View more