how/where to use calculated column in proc sql union

Ccasagran737 — Wed, 29 Jul 2020 06:06:27 GMT

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

Re: how/where to use calculated column in proc sql union

Kurt_Bremser — Wed, 29 Jul 2020 08:26:42 GMT

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.

Re: how/where to use calculated column in proc sql union

Ccasagran737 — Wed, 29 Jul 2020 13:56:29 GMT

Great! thanks for your help!

topic Re: how/where to use calculated column in proc sql union in SAS Programming

how/where to use calculated column in proc sql union

Re: how/where to use calculated column in proc sql union

Re: how/where to use calculated column in proc sql union