Generate new difficult variable that count observations based on location

[Mikkel_madsen]

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

[FreelanceReinh]

I found these issues:

1. The closing parenthesis after 0.360 must be moved to the end of the subquery, i.e., you need two closing parentheses before as d0_360. Of course, the other two subqueries need the same correction. Apparently, this was an unbalanced and unnecessary parenthesis in my (untested) suggested WHERE condition. Sorry for that.
2. You forgot the quotation marks around the string Y, so SAS interpreted this as a variable name. Hence the error message "columns were not found."
3. Interesting: I encountered severe error messages such as

   ERROR: An exception has been encountered.
   Please contact technical support and provide them with the following traceback information:

   The SAS task name is [SQL]
   ERROR: Read Access Violation SQL
   Exception occurred at (0D1D4752)
   Task Traceback
   which I could resolve by avoiding the -l modifier in the format specification downame.-l!
   Do you have a similar problem after correcting the other issues? Then this might be a bug in PROC SQL (!). The -l modifier works well in other PROC SQL code I've tested.
   In any case, I would simply use the LEFT function instead (see code below).

The variable names incident_northing and incident_easting have never occurred in sample data you provided. I assume they are from dataset Final_database.

Just a simplification: There's no need for using name literals with the valid variable names open and close. (i.e., b.open and b.close or even open and close would be sufficient).

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;

The above code runs without error messages with test data I created, but I cannot be sure that your data has the same structure.

[Mikkel_madsen]

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! 🙂 

[FreelanceReinh]

---

@Mikkel_madsen wrote:

(...)

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)

---

However, in an earlier post of this thread you wrote

3) The variables "Open" and "Close" are both numeric variables with the format TIME8. and the informat TIME11. 

(and my suggested code relied on that statement).

The contradiction is obvious, but the error messages suggest that the two variables are in fact character variables which, of course, requires a conversion before a comparison with numeric values such as timepart(...).

input(b.open,time8.)<=timepart(a.eventtime)<=input(b.close,time8.)

Now you can imagine how useful representative test data (in the form of a DATA step) would have been ...

[Mikkel_madsen]

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.   

[FreelanceReinh]

Hi @Mikkel_madsen,

No problem. :-)  The "Expecting a )" message mostly indicates that there are unbalanced parentheses (which can happen easily in a SELECT expression as complex as yours). If you're using the Enhanced Editor in SAS under Windows there's a convenient feature to find matching parentheses (or square brackets or curly braces): see the keyboard shortcuts "Ctrl + [" and "Ctrl + ]" in section "Navigation" in Keyboard Shortcuts within the Enhanced Editor. (In my experience some of the shortcuts depend on the keyboard layout if it's a non-US keyboard. But even if a particular shortcut doesn't seem to work on your keyboard, you can always assign the respective function to a newly defined shortcut under Tools --> Options --> Enhanced Editor Keys.)

Starting with the PROC SQL step from your previous post, after replacing the inequalities involving open and close with the new version all parentheses would be balanced (as they were before). Maybe you deleted a closing parenthesis accidentally when you pasted the new part of the code? The three expressions before the keyword "as" (i.e., in "as d0_360" etc.) should now end with three closing parentheses in a row.

Regarding test data (in general): For code development and testing purposes it is sufficient to have a dataset with the same structure (in particular variable types and lengths, possibly formats) after restricting it to relevant variables. That is, both variable names and values can be replaced, if necessary, so as to remove all confidential information contained in them.

[Mikkel_madsen]

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