topic Re: How can I create a new variable with the total sum of different observations in SAS Programming

How can I create a new variable with the total sum of different observations

Nina4 — Thu, 23 Sep 2021 13:52:00 GMT

Hi everyone

I have a sample of patient with their medicine they bought. Some patients bought the same medicine several times. I would like to create a variable, where you can see the number of different medications they take (not the number of medicines they bought).

Patient_id	medicine	No_medicine (new variable, how it should be)
1	a	2
1	a	2
1	b	2
2	b	2
2	c	2
3	a	2
3	a	2
3	d	2
4	c	1
5	d	2
5	e	2
5	d	2
6	b	1
7	e	1
7	e	1
8	c	4
8	d	4
8	e	4
8	f	4
9	a	2
9	e	2
10	e	1

I already tried:

data practice2; set practice1;
No_medicine+1;
by patient_id medicine;
if first.patient_id or first.medicine=1;
run;

But the result was nonsense.

How can I create this variable?

Thank you!

Re: How can I create a new variable with the total sum of different observations

PaigeMiller — Thu, 23 Sep 2021 14:03:48 GMT

proc sql;
     create table practice2 as select patient_id,
        medicine,
        count(distinct medicine) as no_medicine
        from practice1
     group by patient_id;
quit;

Re: How can I create a new variable with the total sum of different observations

Nina4 — Thu, 23 Sep 2021 16:28:18 GMT

Dear Paige

Thank you for your solution. It works! 🙂