topic Proc sql in SAS Enterprise Guide

Proc sql

sonaaas — Sun, 12 Feb 2017 03:15:51 GMT

Hi Folks,

I have got one requirement in which I need to fetch the data from the oracle tables.

Lets suppose I have 5 years of data in the table.

A loop will run for 5 years and when the loop continues for a year ,the subloop will run for 12 months and during the inner loop run ,month and year condition will be applied to the table (under proc sql) filter condition.

Any example ,how to do this

Re: Proc sql

LaurieF — Sun, 12 Feb 2017 03:52:48 GMT

No idea I'm afraid. Yet! Can you show some example data, and how you'd like it to appear. Otherwise we're just making random guesses.

Re: Proc sql

Reeza — Sun, 12 Feb 2017 07:30:18 GMT

See the examples in the Macro Appendix, Example 11 is a starter.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Also, look into CALL EXECUTE

Re: Proc sql

Patrick — Mon, 13 Feb 2017 06:02:29 GMT

You will need to be more specific in what you have and what you need in order for us to give you a detailed answer.

On a high level: I'd probably consider to generate the SQL with all the filter conditions and then query the Oracle table only once. If you're going for some looping approach then make sure that your date variables in the filter condition are indexed as else you're heading for 5*12 full table scans which can lead to a very poor query performance.