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
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
;
@Nina4 wrote:
Hey everybodyI'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.
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?
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;
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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.