Looping SQL statements

[jwfrederick]

Tom,

I am interested in looping over an SQL statement, as I want to process 300+ indicators for MSAs. Presently, I am reading from an odbc source, with a command like below:

data(format=odbc, $
sql="select date, value as URU from la_msa_all_data where area_text like 'houston%' and measure_text like '%rate' order by date")


I would like to have a list of MSA codes and be able to loop over them within the sql statement.

Replace "area_text like 'houston%'" with "area_code = '10101'" for example, but I need to loop over that '10101'. Is there a way to define a variable to do this? Or do you have another suggestion for reading in this data?

Thanks!

Regards,

Jason

[moderator]

If you want to the DATA instructions in a loop, so that the series variables are replaced each time by the new data, I would recommend using DISPLAY instructions to build up the SQL string into a variable, then use that variable as the argument for the SQL option.

For example, suppose you have N area codes, and fill a vector of labels called AREA with the codes. You can do something like:

compute prefix = "select date, value as URU from la_msa_all_data where area_code = "
compute suffix = "and measure_text like '%rate' order by date"

do c=1,n
compute sqlstring = prefix+&area(c)+suffix
data(format=odbc,sql=sqlstring)
...

end do

where the & character tells RATS to take the value stored in AREA(c). As a check, you may want to star by doing something like:

compute c = 1
compute sqlstring = prefix+&area(c)+suffix
display sqlstring

to make sure the string is correct.

If you instead want to read in all the data at once (i.e. into separate series for each area code), you could use a similar approach, looping over DISPLAY with a UNIT option to write each line (as part of a long sequence of SQL commands) to an external text file. You can then open the SQL string file and process the whole sequence using the QUERY option to refer to the unitname used when opening the SQL file:

OPEN SQLINPUT SQL.TXT
OPEN ODBC "BLIS2"
DATE(FORMAT=ODBC,QUERY=sqlinput)

Regards,
Tom Maycock

[jwfrederick]

Thanks Tom.

But, I am having one more challenge.

If I include the data read commands within the loop, and then instructions after the data read commands but before the end of the loop, I get the following error message.

## SX11. Identifier URU is Not Recognizable. Incorrect Option Field or Parameter Order?

This is because it is not reading the data before checking the instructions (URU is a series name after reading from a database). For example, I have:

do loopc = 1,2
open data ...
data ...
close data ...

x11(...) URU
end do

If I put the end do before the x11 command, then I can read the data, but this defeats the purpose of the loop.

I envisioned being able to load all the relevant series for each MSA, process a set of commands, write an output file (or append to a file), and then loop to a new MSA. But, I must be doing something incorrect. Would it be better to read all the 1200 series in at once (4 per MSA) and then loop to do the instructions?

Thanks again.