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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.