BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gutst
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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;
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
gutst
Calcite | Level 5
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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 975 views
  • 2 likes
  • 3 in conversation