topic Creating a new variable that incorporates multiple rows in New SAS User

Creating a new variable that incorporates multiple rows

Nina4 — Mon, 18 Oct 2021 14:37:20 GMT

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

Re: Creating a new variable that incorporates multiple rows

PaigeMiller — Mon, 18 Oct 2021 15:01:18 GMT

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

Re: Creating a new variable that incorporates multiple rows

Nina4 — Mon, 18 Oct 2021 15:07:20 GMT

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?

Re: Creating a new variable that incorporates multiple rows

PaigeMiller — Mon, 18 Oct 2021 15:17:10 GMT

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;

Re: Creating a new variable that incorporates multiple rows

Tom — Mon, 18 Oct 2021 15:17:29 GMT

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
;