topic Re: How to merge appended values? in SAS Programming

How to merge appended values?

EC27556 — Thu, 15 Oct 2020 15:12:04 GMT

I currently have a situation where I have:

ID Cost

1 10

1 5

2 10

3 10

What code can I use to convert the above into:

ID Cost

1 15

2 10

3 20

Thanks!

Re: How to merge appended values?

Kurt_Bremser — Thu, 15 Oct 2020 15:21:15 GMT

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

Re: How to merge appended values?

mklangley — Thu, 15 Oct 2020 15:27:13 GMT

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;

Re: How to merge appended values?

Reeza — Thu, 15 Oct 2020 15:39:15 GMT

PROC MEANS.

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

Re: How to merge appended values?

EC27556 — Thu, 15 Oct 2020 15:42:46 GMT

Fab, thanks 🙂

Re: How to merge appended values?

EC27556 — Thu, 15 Oct 2020 16:02:42 GMT

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!)

Re: How to merge appended values?

mklangley — Thu, 15 Oct 2020 16:11:14 GMT

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.

Re: How to merge appended values?

Kurt_Bremser — Thu, 15 Oct 2020 17:21:56 GMT

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

(the "guess" is because of the pronomen)

Re: How to merge appended values?

Reeza — Thu, 15 Oct 2020 17:03:11 GMT

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;

Re: How to merge appended values?

EC27556 — Thu, 15 Oct 2020 18:06:08 GMT

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!

Re: How to merge appended values?

Reeza — Thu, 15 Oct 2020 18:34:00 GMT

Add the following line: ods output summary=want;