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 |
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: