parity at sampling point

[lone0708]

Hi All, 

I am working with a dataset containing information about dates of bloodsamples in a cohort of women who have been pregnant one or more times. I need to calculate parity of the woman at sampling point. 

data have;
input woman_id date_of_delivery woman_birth_date sampling date;
datalines;
1 21-dec-2001 01-jan-1971 12-jun-2020
1 01-jan-2003 01-jan-1971 12-jun-2020
1 07-aug-2005 01-jan-1971 12-jun-2020
1 13-jun-2009 01-jan-1971 12-jun-2020
2 07-Jan-2010 15-APR-1970 10-may-2009
2 07-Jan-2010 15-APR-1970 12-may-2021
2 07-Jan-2010 15-APR-1970 14-may-2021
3 26-jan-1980 07-aug-1961 18-jan-1979
3 26-jan-1980 07-aug-1961 20-jan-1979
3 26-jan-1980 07-aug-1961 14-mar-2021
3 26-jan-1980 07-aug-1961 17-mar-2021
3 10-aug-1983 07-aug-1961 18-jan-1979
3 10-aug-1983 07-aug-1961 20-jan-1979
3 10-aug-1983 07-aug-1961 14-mar-2021
3 10-aug-1983 07-aug-1961 17-mar-2021
3 12-sep-1985 07-aug-1961 18-jan-1979
3 12-sep-1985 07-aug-1961 20-jan-1979
3 12-sep-1985 07-aug-1961 14-mar-2021
3 12-sep-1985 07-aug-1961 17-mar-2021
;
run;

data WANT; 
input woman_id date_of_delivery woman_birth_date sampling date parity; 
datalines;
1 21-dec-200101-jan-1971 12-jun-2020 4
1 01-jan-2003 01-jan-1971 12-jun-2020 4 
1 07-aug-2005 01-jan-1971 12-jun-2020 4
1 13-jun-2009 01-jan-1971 12-jun-2020 4
2 07-Jan-2010 15-APR-1970 10-may-2009 0
2 07-Jan-2010 15-APR-1970 12-may-2021 1
2 07-Jan-2010 15-APR-1970 14-may-2021 1
3 26-jan-1980 07-aug-1961 18-jan-1979 0
3 26-jan-1980 07-aug-1961 20-jan-1979 0
3 26-jan-1980 07-aug-1961 14-mar-2021 4
3 26-jan-1980 07-aug-1961 17-mar-2021 4
3 10-aug-1983 07-aug-1961 18-jan-1979 0
3 10-aug-1983 07-aug-1961 20-jan-1979 0
3 10-aug-1983 07-aug-1961 14-mar-2021 4
3 10-aug-1983 07-aug-1961 17-mar-2021 4
3 12-sep-1985 07-aug-1961 18-jan-1979 0
3 12-sep-1985 07-aug-1961 20-jan-1979 0
3 12-sep-1985 07-aug-1961 14-mar-2021 4
3 12-sep-1985 07-aug-1961 17-mar-2021 4
;
run;

[FreelanceReinh · Accepted solution]

Hi @lone0708,

If "parity" was defined as the number of deliveries (on or) before the sampling date, I would suggest this:

proc sql;
create table want as
select *, (select count(distinct date_of_delivery) from have where woman_id=a.woman_id & date_of_delivery<=a.sampling_date) as parity
from have a;
quit;

But for the most recent samples of woman_id 3 (with three dates of delivery) this yields parity=3, not 4, so I'm not sure I understand your definition correctly.

[lone0708]

You are right... i wrote the wrong parity.
Thank you very much for the code, i will just see if i can get it to work!