Unexpected values, garbage characters, or the wrong variable's value might be inserted into columns with the SQL procedure when the following are true:
- One of your values that is inserted is a constant.
- One of your values is the result of a summary function.
- Your query includes the DISTINCT argument.
- Your query includes a GROUP BY clause where the constant variable is not included.
- Your input table is a SAS data set.
- The table being inserted into can be either a SAS data set or a database table.
Here is an example query that might generate the incorrect results:
proc sql;
create table output_tab (
v1 num(4) format=4. informat=4.,
v2 char(10),
v3 num(8) format=13.)
;
quit;
data input_tab;
input v2 $ v3 13. ;
cards;
1111111111 111
2222222222 222
;
run;
proc sql;
insert into output_tab(v1, v2, v3)
select distinct 3020, v2, sum(v3)
from input_tab
group by v2;
quit;
Here is the expected output for the example query:
Obs V1 V2 V3
1 3020 11111111 111
2 3020 22222222 222
The incorrect output might look similar to the following where, in this execution, V1 has an unexpected value, V2 is garbage, and V3 is the output that should have been in V1:
Obs V1 V2 V3
1 0 À[@ 3020
2 0 Àk@ 3020
Which variables contain incorrect output might vary depending on the exact query and release of SAS® that you run.
Workarounds
One workaround is to include the position of the constant in the SELECT clause of the INSERT statement to the GROUP BY clause:
proc sql;
insert into output_tab(v1, v2, v3)
select distinct 3020, v2, sum(v3)
from input_tab
group by 1, v2;
quit;
Another workaround is to remove the DISTINCT option:
proc sql;
insert into output_tab(v1, v2, v3)
select 3020, v2, sum(v3)
from input_tab
group by v2;
quit;