CODEWITHSUNDEEP
sasstatistics
MaBo88
MaBo88

Reputation: 97

How to merge rows in SAS if several conditions apply?

I need to check my dataset for equal values in three columns and then add the two rows which both meet that condition. The output should look like the rows before but having one cell that has the summed up value.

Example:

Security date flag volume CUSIP


A 20150707 1 100 xxx

B 20150708 0 100 xyz

B 20150708 0 200 xyz

B 20150709 1 150 xyz

C 20150709 0 100 yyy

C 20150710 1 200 yyy

C 20150710 1 300 yyy

D 20150710 2 100 zzz

Output:

A 20150707 1 100 xxx

B 20150708 0 300 xyz 

B 20150709 1 150 xyz

C 20150709 0 100 yyy

C 20150710 1 500 yyy

D 20150710 2 100 zzz

I find difficulties comparing two rows and then summing them up into a new variable.

Upvotes: 0

Views: 316

Answers (1)

udden2903
udden2903

Reputation: 783

Hopefully this accomplishes what you are looking for.

data have ;
  input ColA $ ColB $ ColC ColD ColE $;
cards;
A 20150707 1 100 xxx

B 20150708 0 100 xyz

B 20150708 0 200 xyz

B 20150709 1 150 xyz

C 20150709 0 100 yyy

C 20150710 1 200 yyy

C 20150710 1 300 yyy

D 20150710 2 100 zzz

;

proc sql;
    create table want as
        select distinct ColA, ColB, colC, sum(ColD) as sumColD, colE
            from have
                group by ColA, colB
                    order by ColA, colB;
quit;

Upvotes: 1

Related Questions