hi I am trying to think through this problem: I need to calculate 'in each unique pregnancy, at which week in pregnancy a woman got a vaccine.'
- a unique eposide is defined as: distinct person_id, condition_id (so if two records for person 1, person_id = 1, condition_id =1 and person_id =1, condition_id = 2, means this women has two distinct pregnancies; if one record of person_id = 1, condition_id =2, this person has one distinct pregnancy)
I want to fill in a table that looks like:
All pregnancy episodes | week0-12 | week13-19 | week 20 | week21 |
number under all pregnancy episodes should = sum of all the week column.
BUT my data shows that during some pregnancy episodes, some women got more than one vaccine, and I only want each unique pregnancy being count once, so that the sum of all weeks can equal to all unique pregnancies. I want to calculate according to this rule:
For all the episodes that has more than one vaccines weeks (week when they got their vaccine)
- If 1 vaccine is in 27-36 week, and others are not in week 27-36, then use the week in 27-36
- if multiple vaccine in 27-36, then use the earliest week
- if neither vaccine is in 27-36, then use the earliest week
My input data looks like:
person_id | condition_id | week |
1 | 1 | 26 |
1 | 2 | 4 |
1 | 2 | 30 |
2 | 1 | 15 |
3 | 1 | 14 |
3 | 1 | 28 |
3 | 2 | 28 |
3 | 2 | 32 |
4 | 1 | 18 |
5 | 2 | 32 |
6 | 1 | 33 |
6 | 2 | 34 |
6 | 2 | 17 |
My desired output is the red rows in input table above, in other words, I want to leave only one row per unique pregnancy after applying the rule for multiple week entries under one pregnancy.
Any suggestion is appreciated!
Thank you!
something like this
data have;
input person_id condition_id week ;
datalines;
1 1 26
1 2 4
1 2 30
2 1 15
3 1 14
3 1 28
3 2 28
3 2 32
4 1 18
5 2 32
6 1 33
6 2 34
6 2 17
;
run;
proc sql;
select person_id, condition_id, week from have
group by person_id , condition_id
having max(week) = week;
quit;
Thanks @kiranv_, but this code won't work for this customized value assign, I don't need max(week), I need to select first which week range it's in, then depends on that, I need to assign its own value or the earliest value
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.