Creating a new variable that incorporates multiple rows

[Nina4]

Hey everybody 

I'm working with claims data, where each patient has multiple rows. I would now like to create a new variable with a yes or no (1/0) per patient. 

This is my exercise data:

data exercise;
input patient medication$ expensive no_rows;
cards;
1 a 1 1
1 b 0 1
1 a 1 1
2 c 0 1
2 d 0 1
2 a 1 1
3 b 1 1
3 a 1 1
3 c 0 1
4 a 1 1
5 b 1 1
6 d 0 1
6 d 0 1
;
run;

Now you can see, that I coded each medicine as expensive yes or no (1/0). I would like to know how I can add a variable that tells me if the patient has an expensive medicine. 

As a table: 

Patient	Medication	expensive	no_row	exp_pat
1	a	1	1	1
1	b	0	1	1
1	a	1	1	1
2	c	0	1	1
2	d	0	1	1
2	a	1	1	1
3	b	1	1	1
3	a	1	1	1
3	c	0	1	1
4	a	1	1	1
5	b	1	1	1
6	d	0	1	0
6	d	0	1	0

Thank you for your answers and kind regards, 

Nina

[PaigeMiller]

---

@Nina4 wrote:

Hey everybody 

I'm working with claims data, where each patient has multiple rows. I would now like to create a new variable with a yes or no (1/0) per patient. 

This is my exercise data:

data exercise;
input patient medication$ expensive no_rows;
cards;
1 a 1 1
1 b 0 1
1 a 1 1
2 c 0 1
2 d 0 1
2 a 1 1
3 b 1 1
3 a 1 1
3 c 0 1
4 a 1 1
5 b 1 1
6 d 0 1
6 d 0 1
;
run;

Now you can see, that I coded each medicine as expensive yes or no (1/0). I would like to know how I can add a variable that tells me if the patient has an expensive medicine. 

---

Please explain further. It seems to me you already have a variable that tells you if the patient has an expensive medicine.

[Nina4]

Hi Paige 

I have a variable that tells me if the medicine is expensive. But each patient has multiple medicines (in multiple rows) and I would like to have a variable that tells me if a patient has (at least) one expensive medicine. 

Is this clearer?

[PaigeMiller]

Thanks, yes that helps.

proc summary nway data=exercise;
    class patient;
    var expensive;
    output out=_max_(drop=_:) max=exp_pat;
Run;
data want;
    merge exercise _max_;
    by patient;
Run;

[Tom · Accepted solution]

To test if any boolean value is true use the MAX() aggregate function.

proc sql;
 create table want as 
   select *, max(expensive) as exp_pat
   from exercise
   group by patient
;