Help using Base SAS procedures

Roll up Criteria

Reply
Frequent Contributor
Posts: 90

Roll up Criteria

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

PROC Star
Posts: 7,474

Roll up Criteria

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;

Valued Guide
Posts: 765

Re: Roll up Criteria

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;

Ask a Question
Discussion stats
  • 2 replies
  • 147 views
  • 0 likes
  • 3 in conversation