With the SET you have constructed, a SAS ERROR condition is generated when a given SAS variable has a NUMERIC / CHARACTER type conflict in two or more datasets listed on the SET statement.
And, do consider that SAS will simply truncate a variable's length when the "type" conflict is not present but a "shorter length" with a WARNING message, that sometimes goes unnoticed.
My general approach (and recommendation) is to define either a AUTOCALL macro or INCLUDE member with specific LENGTH, FORMAT and LABEL definitions, however I do not typically have external DBMS data feeds to consider.
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.