Proc SQL and options

[1162]

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?

Example:
[pre]
PROC SQL;
SET TEMPORARY OPTION Temp_Extract_Column_Delimiter = ',';
SET TEMPORARY OPTION Temp_Extract_Row_Delimiter = '';
SELECT *
FROM mydatabasetable;
QUIT;
[/pre]

[LinusH]

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.

Regards,
Linus

[1162]

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.

[sbb]

Current SAS 9.2 DOC for SAS/ACCESS for Sybase link below. Also suggest searching the SAS support website at http://support.sas.com/ for technical papers related to the topics you have referenced.

Scott Barry
SBBWorks, Inc.

SAS/ACCESS for Sybase:
http://support.sas.com/documentation/cdl/en/acreldb/59618/HTML/default/a002299547.htm

[LinusH]

In your case, try to put your Sybase statements within execute ( ) blocks within proc sql:

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;
QUIT;

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.

Regards,
Linus

[1162]

Just to update everyone, the EXECUTE statement worked when using the SAS pass-through facility.