topic Re: How to sum up values within individual patients (several rows per patient) in SAS Programming

How to sum up values within individual patients (several rows per patient)

gutst — Sat, 08 May 2021 18:02:46 GMT

I have a dataset with drug claims (data drugclaims). Each row represents one drug claim and includes a variable that specifies the total dose dispensed (cum_dose). I need to create a new variable for each patient showing the total cumulative dose of the drug per distinct patient within the dataset (i.e. sum up cum_dose of the same e_vp_id). Finally this needs to be weighted by the variable hrf_b. Below is an example of the dataset I have (drugclaims):

Obs	e_vp_id	hrf_b	cum_dose
1	671692534	5.80214515	25’000
2	671693823	5.2234011	25’000
3	671787871	12.4227981	10’000
4	671787871	12.4227981	25’000
5	671787871	12.4227981	5’000
6	671898982	5.10388071	25’000
7	671898982	5.10388071	25’000
8	672010043	12.0596382	25’000
9	672010043	12.0596382	15’000
10	672010067	10.2165108	5’000

This is the new dataset I need with the new variable (dose_total):

Obs	e_vp_id	hrf_b	cum_dose	dose total
1	671692534	5.80214515	25’000	2’500
2	671693823	5.2234011	25’000	2’500
3	671787871	12.4227981	10’000	40’000
4	671787871	12.4227981	25’000	40’000
5	671787871	12.4227981	5’000	40’000
6	671898982	5.10388071	25’000	50’000
7	671898982	5.10388071	25’000	50’000
8	672010043	12.0596382	25’000	35’000
9	672010043	12.0596382	15’000	35’000
10	672010067	10.2165108	5’000	5’000

Any tips are welcome. Thank you in advance!

Re: How to sum up values within individual patients (several rows per patient)

novinosrin — Sat, 08 May 2021 21:51:01 GMT

Hi @gutst A simple double pass aka Auto remerge functionality of Proc SQL?

proc sql;
 create table want as
 select *, sum(cum_dose) as dose_total
 from your_dataset
 group by e_vp_id;
quit;

Re: How to sum up values within individual patients (several rows per patient)

PaigeMiller — Sat, 08 May 2021 22:11:51 GMT

@gutst wrote:

I have a dataset with drug claims (data drugclaims). Each row represents one drug claim and includes a variable that specifies the total dose dispensed (cum_dose). I need to create a new variable for each patient showing the total cumulative dose of the drug per distinct patient within the dataset (i.e. sum up cum_dose of the same e_vp_id). Finally this needs to be weighted by the variable hrf_b. Below is an example of the dataset I have (drugclaims):

Obs

e_vp_id

hrf_b

cum_dose

1

671692534

5.80214515

25’000

2

671693823

5.2234011

25’000

3

671787871

12.4227981

10’000

4

671787871

12.4227981

25’000

5

671787871

12.4227981

5’000

6

671898982

5.10388071

25’000

7

671898982

5.10388071

25’000

8

672010043

12.0596382

25’000

9

672010043

12.0596382

15’000

10

672010067

10.2165108

5’000

This is the new dataset I need with the new variable (dose_total):

Obs

e_vp_id

hrf_b

cum_dose

dose total

1

671692534

5.80214515

25’000

2’500

2

671693823

5.2234011

25’000

2’500

3

671787871

12.4227981

10’000

40’000

4

671787871

12.4227981

25’000

40’000

5

671787871

12.4227981

5’000

40’000

6

671898982

5.10388071

25’000

50’000

7

671898982

5.10388071

25’000

50’000

8

672010043

12.0596382

25’000

35’000

9

672010043

12.0596382

15’000

35’000

10

672010067

10.2165108

5’000

5’000

Any tips are welcome. Thank you in advance!

You say the dose total should be weighted by HRF_B, but in your output data set it is not weighted. Please explain.

Re: How to sum up values within individual patients (several rows per patient)

gutst — Sat, 08 May 2021 23:16:23 GMT

The displayed table was an example how it could look like. I will now use the "weight" command in the next programming step. @novinosrin @PaigeMiller Thanks for the help!