topic Re: Roll up Criteria in SAS Procedures

Roll up Criteria

raveena — Mon, 03 Oct 2011 15:23:02 GMT

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 — Mon, 03 Oct 2011 16:07:20 GMT

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 — Mon, 03 Oct 2011 19:36:47 GMT

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;