Roll up Criteria

[raveena]

Hi ,

I have two tables Table 1 and Table 2,
need to rollup from table 1 Covunit field value to Table 2  totunits field where rev=N

Table 1:

In table 1, we have ID that has multiple line no.

ID LINENO pos covunit rev
1   1     23    1     N
1   2     23    1     N
1   3     23    1     N
1   4     23    1     N

2   1     23    1     N
2   2     23    1     N

3   3     23    1     N

4   5     23    1     Y

Table 2:

In table 2, the default value for lines is 0.

ID lineNo totunits
1   0   
2   0
3   0
4   0

Output should be:

ID lineno Totunits
1   0      4
2   0      2
3   0      1
4   0

Please help me out on this logic.

Thanks in Advance

[art297]

There are a number of ways to do what you want.  The following assumes that table2 is sorted in ID order:

proc freq data=table1 (where=(rev eq 'N'));

  tables id*rev/out=sums (keep=ID count

         rename=(count=totunits));

run;

data table3;

  merge table2 sums;

  by id;

run;

[MikeZdeb]

Hi ... another idea ...

proc sql;

create table t3 as

select id, lineno, totunits from

(select id as xid , sum(covunit) as totunits from t1 (where=(rev eq 'N')) group xid) right join t2

on xid eq id;

quit;