topic Re: PROC SQL merging non-matching dates in New SAS User

PROC SQL merging non-matching dates

radhikaa4 — Mon, 15 Apr 2019 14:36:37 GMT

Hello, I need help merging the dates that do not match for 2 different datasets

Dataset #1: Screening

Group	Screen_date	Total_screen
A	1/1/2018	1
A	2/1/2018	2
A	3/1/2018	2
A	5/1/2018	3

Dataset #2: Randomizaiton

Group	rand_date	Total_rand
A	1/1/2018	1
A	2/1/2018	2
A	4/1/2018	2
A	5/1/2018	1

Want:

Group	New_date	Total_screen	Total_random
A	1/1/2018	1	1
A	2/1/2018	2	1
A	3/1/2018	2
A	4/1/2018		2
A	5/1/2018	3	1

I tried the following:

PRoc sql;

SELECT a.*

FROM dataset_1 a LEFT JOIN dataset_2 b ON a.group = b.group

where a.screen_date = b.rand_date

quit;

this gives me matched data: so 3 records match but not 2.

Thankss!!

Re: PROC SQL merging non-matching dates

novinosrin — Mon, 15 Apr 2019 14:50:33 GMT

HI @radhikaa4 I think you need a full join from What I could deduce on seeing your WANT dataset


data one;
input Group	$ Screen_date :mmddyy10.	Total_screen;
format Screen_date mmddyy10.;
cards;
A	1/1/2018	1
A	2/1/2018	2
A	3/1/2018	2
A	5/1/2018	3
;

data two;
input Group $	rand_date :mmddyy10.	Total_rand;
format rand_date mmddyy10.;
cards;
A	1/1/2018	1
A	2/1/2018	2
A	4/1/2018	2
A	5/1/2018	1
;

proc sql;
create table want as
SELECT coalesce(a.group,b.group) as group,coalesce(a.Screen_date,b.rand_date) as Screen_date format=mmddyy10.,Total_screen
FROM one a full JOIN two b 
ON a.group = b.group and a.Screen_date=b.rand_date;
quit;

Re: PROC SQL merging non-matching dates

LinusH — Mon, 15 Apr 2019 14:52:16 GMT

Try a FULL JOIN, together with coalesce() functino for GROUP and NEW_DATE.