topic merging two datasets in SAS Programming

merging two datasets

beckwaller — Tue, 23 Apr 2019 23:54:31 GMT

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

Re: merging two datasets

PGStats — Wed, 24 Apr 2019 01:51:54 GMT

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.

Re: merging two datasets

beckwaller — Wed, 24 Apr 2019 01:48:11 GMT

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.

Re: merging two datasets

beckwaller — Wed, 24 Apr 2019 01:49:35 GMT

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

Re: merging two datasets

PGStats — Wed, 24 Apr 2019 01:52:42 GMT

Please try the new version above.

Re: merging two datasets

beckwaller — Wed, 24 Apr 2019 01:58:32 GMT

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

Re: merging two datasets

FreelanceReinh — Wed, 24 Apr 2019 08:01:22 GMT

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.