DATA Step, Macro, Functions and more

how to apply a customized data selection/value assign rule?

Reply
Contributor
Posts: 38

how to apply a customized data selection/value assign rule?

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 episodesweek0-12week13-19week 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_idcondition_idweek
1126
124
1230
2115
3114
3128
3228
3232
4118
5232
6133
6234
6217

 

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!

PROC Star
Posts: 548

Re: how to apply a customized data selection/value assign rule?

Posted in reply to LisaYIN9309

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;
Contributor
Posts: 38

Re: how to apply a customized data selection/value assign rule?

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

Ask a Question
Discussion stats
  • 2 replies
  • 116 views
  • 0 likes
  • 2 in conversation