topic Re: Merging 2 datasets in Programming 1 and 2

Merging 2 datasets

monday89 — Tue, 17 Nov 2020 17:46:16 GMT

Hi I have two dasets and I need to combine them both even though dates for the both tables do not match

Site	DTM	measure1	measure2
ABC	2/2/2020 9:53	32.1	20.5
ABC	2/2/2020 10:31	12.1	12.6
DEF	2/3/2020 9:33	42.1	44.1
DEF	2/4/2020 14:23	31.1	41.4

Site	DTM	measure1
ABC	2/2/2020 9:53	32.1
ABC	2/2/2020 10:31	12.1
ABC	2/3/2020 9:33	42.1
DEF	2/2/2020 9:53	41.1
DEF	2/2/2020 10:31	12.2
DEF	2/3/2020 9:33	42.1
DEF	2/4/2020 14:23	31.1

Want:

Site	DTM	measure1	measure2
ABC	2/2/2020 9:53	32.1	20.5
ABC	2/2/2020 10:31	12.1	12.6
ABC	2/3/2020 9:33	42.1
DEF	2/2/2020 9:53	41.1
DEF	2/2/2020 10:31	12.2
DEF	2/3/2020 9:33	42.1	44.1
DEF	2/4/2020 14:23	31.1	41.4

Here's my attempt thus far:

select b.site, b.measure1, b.measure2, b.dtm, a.measure1

from dataset_a a
left outer join dataset_b on a.site = b.site

Re: Merging 2 datasets

PeterClemmensen — Tue, 17 Nov 2020 17:56:56 GMT

"even though dates for the both tables do not match". What do you mean by that? The dates do match?

Re: Merging 2 datasets

monday89 — Tue, 17 Nov 2020 17:58:39 GMT

Hi. Yes. They dates dont have to match but I would to show the values of
measure 1 and keep measure 2 as blank where dates from both datasets dont
match

Re: Merging 2 datasets

PeterClemmensen — Tue, 17 Nov 2020 18:05:42 GMT

I think this is what you want

data a;
input Site $ DTM $ 5-18 measure1 measure2;
datalines;
ABC 2/2/2020 9:53  32.1 20.5
ABC 2/2/2020 10:31 12.1 12.6
DEF 2/3/2020 9:33  42.1 44.1
DEF 2/4/2020 14:23 31.1 41.4
;

data b;
input Site $ DTM $ 5-18 measure1;
datalines;
ABC 2/2/2020 9:53 32.1
ABC 2/2/2020 10:31 12.1
ABC 2/3/2020 9:33 42.1
DEF 2/2/2020 9:53 41.1
DEF 2/2/2020 10:31 12.2
DEF 2/3/2020 9:33 42.1
DEF 2/4/2020 14:23 31.1
;

proc sql;
   create table want as
   select b.*, a.measure2
   from b left join a
   on a.Site=b.Site and a.DTM=b.DTM;
quit;

Result:

Site  DTM             measure1  measure2 
ABC   2/2/2020 10:31  12.1      12.6 
ABC   2/2/2020 9:53   32.1      20.5 
ABC   2/3/2020 9:33   42.1      . 
DEF   2/2/2020 10:31  12.2      . 
DEF   2/2/2020 9:53   41.1      . 
DEF   2/3/2020 9:33   42.1      44.1 
DEF   2/4/2020 14:23  31.1      41.4