BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
DataPanda99
Fluorite | Level 6

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

IDDATEMeasure1
1011-Jul-225
1013-Jul-228
1014-Jul-224
1016-Jul-227
1017-Jul-222
...
...
10131-Jul-223

 

Dataset-2

IDDATEMeasure2
1011-Jul-222
1012-Jul-223
1013-Jul-224
1015-Jul-222
1017-Jul-222
...
...
10131-Jul-225


Output desired:

IDDATEMeasure1Measure2
1011-Jul-2252
1012-Jul-22 3
1013-Jul-2284
1014-Jul-224 
1015-Jul-22 2
1016-Jul-227 
1017-Jul-2222
....
....
10131-Jul-2235
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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;
DataPanda99
Fluorite | Level 6
Thank you Patrick!
Kurt_Bremser
Super User

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.

DataPanda99
Fluorite | Level 6
Thank you, Kurt_Bremser!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 657 views
  • 2 likes
  • 3 in conversation