how/where to use calculated column in proc sql union

[Ccasagran737]

Hi!

I am trying to write a proc sql to compute a check variabile that should be ugual to zero, but SAS return to me this error:

ERROR: It appears that the CALCULATED variable gross_cam_t0 was referenced before it was defined.

ERROR: It appears that the CALCULATED variable gross_cam_t1 was referenced before it was defined.

ERROR: The following columns were not found as CALCULATED references in the immediate query: gross_cam_t0, gross_cam_t1.

These are my two input dataset

data have_1;
    input id $2. gross_cam_t0 gross_cam_t1;
datalines;
A1 10 5
B2 1 1
C3 8 11
;
run;

data have_2;
    input delta;
datalines;
-5
0
3
;
run;

This is what I would like to have

data temp;
     input gross_cam_t0_tot gross_cam_t1_tot delta_tot;
datalines;
19 17 -2
;
run;

data want;
    set temp;
    check_ugual_zero = sum(gross_cam_t0_tot,delta_tot,-gross_cam_t1_tot);
run;

And this is my SAS code that dosen't work

proc sql;
     select   sum(gross_cam_t0) as gross_cam_t0 ,
              sum(gross_cam_t1) as gross_cam_t1
     from have_1 outer union corr
        select sum(delta) as delta,
               calculated gross_cam_t0 + calculated delta - calculated gross_cam_t1 as check_ugual_zero
        from have_2;
quit;

If I am not clear pleas write me and I'll give more detail. 

Plese help me to fix this code. Thanks a lot!

Daniel

[Kurt_Bremser · Accepted solution]

Try this:

proc sql;
create table want as
  select
    sum(gross_cam_t0) as gross_cam_t0 ,
    sum(gross_cam_t1) as gross_cam_t1,
    (select sum(delta) from have2) as delta,
    calculated gross_cam_t0 + calculated delta - calculated gross_cam_t1 as check_ugual_zero
  from have_1
;
quit;

Untested, posted from my tablet.

[Ccasagran737]

Great! thanks for your help!