merging two datasets

[beckwaller]

Have a project for school and we are beginners in SAS programming.  We are having a bit of trouble merging an environmental dataset (daily data for 10 years) with a birth cohort dataset (n = ~350,000). Essentially, we want to tell SAS to start in the birth dataset (in which we have a subject ID, county of residence and  pregnancy date range for each individual subject (FirstDayPreg LastDayPreg), then go to the air quality data set and count the number of exceedances (ExpLevel coded as 0 or 1.  1 if exceeds daily threshold for various air quality metrics) within that pregnancy date range, and then output the number of exceedances by subject ID. We have tried to accomplish this with PROC SQL and nested DO loops, but can't quite seem to figure it out. An image of environmental data set and birth cohort data are attached.  We would really appreciate any assistance you can provide us.

Birth cohort dataEnvironmental data

[PGStats]

Assuming that Date, firstPregDay and lastPregDay are SAS dates and that the air quality dataset is complete for the pregnancy periods, the following will do the counts:

proc sql;
create table expCounts as
select
    a.id,
    a.county,
    sum(b.ExpLevel) as expCount
from 
    birth as a inner join
    air as b on 
        a.county=b.county and 
        b.date between a.firstPregDay and a.lastPregDay
group by a.id, a.county;
quit;

(untested)

EDIT Added county to group by clause.

[beckwaller]

Thanks!  I gave the code a shot, but I just get the following.  This was part of our issue.  We just want one value for each subject ID.

[beckwaller]

I think it was because we had to create firstpregday and lastpregday from existing variables and they are not SAS dates

[PGStats]

Please try the new version above.

[beckwaller · Accepted solution]

That worked! Thank you.  We haven't made it to Proc sql yet in class.  I really appreciate your help.

[FreelanceReinh]

Hello @beckwaller,

Glad to see that PGStats's solution worked for you. Then it would help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: see instructions here.