SQL in ArcGIS

Today, I was asked to map a subset of a publicly available dataset. Generally being a fan of SQL, I thought that this might be the quickest way to reduce the data to that which I needed.

However, ArcGIS SQL is not like the SQL we all know, which makes it simple to add a list to a "WHERE" statement:

SELECT * FROM table1 WHERE ppl IN ('James', 'Samuel', 'Jack');

In ArcGIS SQL it is necessary to have one statement per list item, even it all comes from the same column:

SELECT * FROM table1 WHERE "ppl" = 'James' OR "ppl" = 'Samuel' OR "ppl" = 'Jack'

I didn't see any obvious note of this in the ArcGIS documentation and is a curious deviation from normal SQL protocol.

Formatting SAS Dates and Other Variables

Yesterday, I was approached by a coworker to convert dates using SAS that were in the following format:

yyyymmdd

My coworker desired that the data be displayed in this format:

mm/dd/yyyy

The data originated in a .txt file and was being imported using a data step and an input statement. Each field began at the same character on the line. Originally the data was coded to find the data within a range of characters on each line:

data test;
infile test.txt;
input C_O7 $575-579;
run;





However, when inputting code in this manner, it is not possible to designate the type of variable, preventing it from being converted to a date.

Instead, instruct SAS as to where the beginning of each column is, which allows you to specify a data type:

data test;
infile test.txt;
input @42 date yymmdd8.; /* This should match the way the original data is formatted */
C_O7 $575-579; /* You can mix and match how to find the data columns */
format date mmddyy10.; /* This is the format you would like it presented in */
run;





There are many different formats available, and SAS gives you pretty good control over how to format types like dates and currency.