SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How PROC SQL helps you see missing values and other data attributes

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

How PROC SQL helps you see missing values and other data attributes

I am trying to use PROC SQL left join to merge two databases. 

 

The first database (master) is my actual database which has rows indicating time periods during which measurements were taken for each subject; I want to keep ALL of those rows. I want to add in data from the second database (additional) based on whether "additional" has values that correspond to the time periods in each of the rows in "master". If there are no measurements in “additional” that correspond to the specified time periods, I would like for those rows to be retained as missing values, instead of dropped. For instance, for example Subject #001, I want to merge data from “additional” into the “master” database to produce the “want” database below:

 

Master:

StudyID

StartDate_C

EndDate1_C

Date_12mopost

001

01/04/2012

07/10/2012

01/04/2013

001

03/07/2013

06/18/2013

03/07/2014

001

06/25/2013

01/01/2015

06/24/2014

 

Want (desired combined database):

StudyID

StartDate_C

EndDate1_C

Date_12mopost

full_lab_Date

001

01/04/2012

07/10/2012

01/04/2013

.

001

03/07/2013

06/18/2013

03/07/2014

.

001

06/25/2013

01/01/2015

06/24/2014

07/31/2013

001

06/25/2013

01/01/2015

06/24/2014

11/22/2013

 

I used left join (code below), but it looks like this code is still causing rows without matches in “additional” to be dropped:

 

proc sql;

create table want as

select c.*, v.*

from WORK.master AS c left join WORK.additional AS v

on c.StudyID=v.StudyID_copy

where c.StartDate_C <= v.full_lab_date <= c.DATE_12MOPOST ;

;

quit;

 

want (using the code above, which produces something different than what I want):

StudyID

StartDate_C

EndDate1_C

Date_12mopost

full_lab_DATE

001

06/25/2013

01/01/2015

06/24/2014

07/31/2013

001

06/25/2013

01/01/2015

06/24/2014

11/22/2013

 

Is there a modification I can make to my code in order to get the desired outcome?

 

Thank you in advance.


Accepted Solutions
Solution
‎04-25-2017 02:07 PM
PROC Star
Posts: 252

Re: How PROC SQL helps you see missing values and other data attributes

can you change

where c.StartDate_C <= v.full_lab_date <= c.DATE_12MOPOST

to

and c.StartDate_C <= v.full_lab_date <= c.DATE_12MOPOST

View solution in original post


All Replies
Solution
‎04-25-2017 02:07 PM
PROC Star
Posts: 252

Re: How PROC SQL helps you see missing values and other data attributes

can you change

where c.StartDate_C <= v.full_lab_date <= c.DATE_12MOPOST

to

and c.StartDate_C <= v.full_lab_date <= c.DATE_12MOPOST

Frequent Contributor
Posts: 93

Re: How PROC SQL helps you see missing values and other data attributes

Try a full join.  This will keep data where A or B exist.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 128 views
  • 1 like
  • 3 in conversation