How to merge appended values?

[EC27556]

I currently have a situation where I have:

ID           Cost

1               10

1                5

2               10

3               10

3               10

What code can I use to convert the above into:

ID           Cost

1               15

2               10

3               20

Thanks!

[Kurt_Bremser]

In SQL, SELECT id and SUM(cost), GROUP BY id.

[mklangley · Accepted solution]

And just to show explicitly @Kurt_Bremser's answer:

data have;
    input id cost;
    datalines;
1 10
1 5
2 10
3 10
3 10
;
run;

proc sql;
    create table want as
    select id, sum(cost) as cost
    from have
    group by id;
quit;

[EC27556]

Fab, thanks 🙂

[EC27556]

Oh, one quick follow up too, if I were to instead have:

ID Cost1 Cost2

1 10 10

1 5 10

2 10 10

3 10 10

3 10 10



and wanted



ID Cost1 Cost2

1 15 20

2 10 10

3 20 20


I would simply need to add the extra variable, i.e:

proc sql;
create table want as
select id, sum(cost1) as cost1, sum(cost2) as cost2
from have
group by id;
quit;


(sorry if an obvious answer - my SQL skills aren't quite up to scratch!)

[mklangley]

Yes, that's exactly right.

And it's super easy (and faster) to test to see if your hypothesis is correct! Just pull open SAS EG or SAS Studio (or whatever platform you're using) and create your HAVE dataset using a datalines DATA STEP (like I demonstrated in my comment above), and then run the PROC SQL hypothesis to see if it gives the results you're expecting.

[Kurt_Bremser]

What @mklangley said in (I guess) his last post is also known as Maxim 4.

(the "guess" is because of the pronomen)

[Reeza]

And that's where PROC MEANS will shine over SQL. In SQL you'll have to list every variable and statistic separately. If you want a different stat, say the mean or the standard deviation for 10 variables that will get tedious quickly. 

proc means data=have noprint NWAY SUM STACKODS;
by ID;
var cost;
run;

Now, you add 4 cost variables and want both the sum and average? Add MEAN to the PROC statement and change the VAR statement slightly to include all 4 variables. 

proc means data=have noprint NWAY SUM MEAN STACKODS;
by ID;
var cost1-cost4;
run;

[EC27556]

Hi, forgive me, but when I run:

data have;
input id cost1 cost2;
datalines;
1 10 10
1 5 10
2 10 10
3 10 10
3 10 10
;
run;

proc means data=have NWAY SUM MEAN stackods;
by ID;
var cost1 cost2;
run;

I simply get the input table as my output table!

[Reeza]

Add the following line: ods output summary=want;

[Reeza]

PROC MEANS.

proc means data=have noprint NWAY SUM STACKODS;
by ID;
var cost;
run;