The Oracle database I am using is for Water Quality at my agency, and the PROJECT datasets coming down from the Oracle database tend to be large.
My original code did SET all of the datasets, but I realized that I would need a more general solution to implement across other projects.
Everyone will have some sort of data to add through SPECIAL, but the contents of SPECIAL will differ between projects. For example, most projects will want a Total Nitrogen calculation, my project has parameters of light transmission in water that live in a different database, and others might have something like pesticide data.
SPECIAL should always be much smaller than PROJECT. For example, this morning's run for my project had 1.1 million rows in PROJECT, and 50K rows in SPECIAL.
In addition, I need the final result of PROJECT+SPECIAL to be sorted. I tested run and cpu time of SETting the datasets followed by PROC SORT, PROC SQL using OUTER UNION CORR and ORDER BY, and to my surprise, APPEND followed by PROC SORT was the clear winner.
Thanks also for the caution to watch my variables and lengths carefully. For most of the cases, I am using PROC SQL, and defining the value of a couple of fields, like
'C' AS REMARK,
'TN_CALC' AS ANALYTE,
and the text fields in Oracle are of length 20 and 240, respectively. I definitely could specify the LENGTH explicitly like this, but was hoping to not have to do that.
'C' AS REMARK LENGTH=20,
'TN_CALC' AS ANALYTE LENGTH=240,
I'm not familiar with AUTOCALL, so will have to do some reading.
Thanks so much for the suggestions!
Wendy
Message was edited by: WendyT