topic Re: SAS Join Datasets with Calendar dates in SAS Programming

SAS Join Datasets with Calendar dates

DataPanda99 — Tue, 20 Jun 2023 08:05:43 GMT

Hi Folks,

I'm not a everyday SAS user but got stuck with this. Any help is highly appreciated.

I have two datasets 1 & 2 and require to build an output like below using 1 and 2 datasets. Thanks in advance!

Dataset - 1

ID	DATE	Measure1
101	1-Jul-22	5
101	3-Jul-22	8
101	4-Jul-22	4
101	6-Jul-22	7
101	7-Jul-22	2
.	.	.
.	.	.
101	31-Jul-22	3

Dataset-2

ID	DATE	Measure2
101	1-Jul-22	2
101	2-Jul-22	3
101	3-Jul-22	4
101	5-Jul-22	2
101	7-Jul-22	2
.	.	.
.	.	.
101	31-Jul-22	5

Output desired:

ID	DATE	Measure1	Measure2
101	1-Jul-22	5	2
101	2-Jul-22		3
101	3-Jul-22	8	4
101	4-Jul-22	4
101	5-Jul-22		2
101	6-Jul-22	7
101	7-Jul-22	2	2
.	.	.	.
.	.	.	.
101	31-Jul-22	3	5

Re: SAS Join Datasets with Calendar dates

Patrick — Tue, 20 Jun 2023 08:16:13 GMT

Your two source tables must be sorted by id and date for below code to work.

data want;
  merge table1 table2;
  by id date;
run;

To sort a table:

proc sort data=table1;
  by id date;
run;

Re: SAS Join Datasets with Calendar dates

Kurt_Bremser — Tue, 20 Jun 2023 08:47:24 GMT

As an alternative, the SQL solution:

proc sql;
create table want as
  select
    coalesce(t1.id,t2.id) as id,
    coalesce(t1.date,t2.date) as date,
    t1.measure1,
    t2.measure2
  from table1 t1
  full join table2 t2
  on t1.id = t2.id and t1.date = t2.date
;
quit;

Personally, I also favor the data step approach.

Re: SAS Join Datasets with Calendar dates

DataPanda99 — Wed, 21 Jun 2023 01:59:30 GMT

Thank you Patrick!

Re: SAS Join Datasets with Calendar dates

DataPanda99 — Wed, 21 Jun 2023 02:00:20 GMT

Thank you, Kurt_Bremser!