I'd like to run a sql query using SAS. The query I have includes some "SET TEMPORARY OPTION" lines that SAS doesn't seem able to handle. Does anyone know whether these kinds of SQL commands can be used in PROC SQL?
SET TEMPORARY OPTION Temp_Extract_Column_Delimiter = ',';
SET TEMPORARY OPTION Temp_Extract_Row_Delimiter = '';
This seems specific syntax for an other DBMS . You are right, it won't work in proc sql. Some options can be set in the proc sql statment, and using the RESET stament inside proc sql. If you can describe what you want to accomplish it would be easier to help you with more specific answers.
The issues are a little odd (in my opinion), but I'll try to explain. I'm querying a Sybase database. The database has a query restriction of 5 minutes which the administrator won't budge on. Their solution to larger queries is to have me write the output to a flat file which I can import into SAS. Somehow, this output to a flat file does not have the time restriction, but the code is written to run on a Sybase application, not SAS. The query is the same except that these temporary options are defined.
The problem is that I can't incorporate their solution into an automated script. My goal was to try to set the options in PROC SQL statement so that I could generate the flat file and then automatically import it, all within SAS.
In your case, try to put your Sybase statements within execute ( ) blocks within proc sql:
connect to sybase ...;
execute (SET TEMPORARY OPTION Temp_Extract_Column_Delimiter = ',') by sybase;
execute (SET TEMPORARY OPTION Temp_Extract_Row_Delimiter = '') by sybase;
execute (SELECT * FROM mydatabasetable) by sybase;
disconnect from sybase;
This might work from a SAS syntax view. But if this will create your external file or not, you'll have to discuss with some Sybase people. I'm especially suspicious against the select statement. In my eyes Sybase might try to return the result to SAS, which is not feasible when using execute blocks.