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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.