Hi All,
I was trying to merge the following two table, can someone tell me how do I get my desired output? Thanks
I tried the following:
DATA Want;
MERGE Table1(in=a) Table2(in=b);
BY state;
IF a=1;
RUN;
Table 1:
ID State
101 NY
101 TX
Table 2:
State Day Time
NY MON 7:00 - 8:00
NY TUES 10:00 - 10:30
NY WED 11: - 11:45
TX FRI 8:00 - 9:30
TX SAT 10:00 - 11:00
TX MON 9:00 - 11:00
Output:
ID State Day Time
101 NY MON 7:00 - 8:00
101 NY TUES 10:00 - 10:30
101 NY WED 11: - 11:45
101 TX FRI 8:00 - 9:30
101 TX SAT 10:00 - 11:00
101 TX MON 9:00 - 11:00
I want all three observation of each by group (since they are unique observation) in Table2 merge with each observation of Table1.
What have you tried?
Is there any further logic beyond joing by State?
Your code works fine for me, so somethings missing if it's not working for you.
Are you getting an error, or unexpected results somehow?
data table1;
input ID State $;
cards;
101 NY
101 TX
;
run;
data table2;
input State $ Day $ Time $20.;
cards;
NY MON 7:00 - 8:00
NY TUES 10:00 - 10:30
NY WED 11:00 - 11:45
TX FRI 8:00 - 9:30
TX SAT 10:00 - 11:00
TX MON 9:00 - 11:00
;
run;
proc sort data=table1; by state;
proc sort data=table2; by state;
DATA Want;
MERGE Table1(in=a) Table2(in=b);
BY state;
IF a=1;
RUN;
oh sorry, My table1 had more observations that I did not mention before, and that's where I am having problem with. Here you go:
data table1;
input ID State $;
cards;
101 NY
101 TX
103 NY
104 NY
101 TX
;
run;
So what's the expected output for that input data? Please try and specify your problem in detail at the beginning...
Please post your data as a data step, its a pain to type it out each time.
You're doing a Many to Many merge - that's better handled in Proc SQL rather than in a data step. I suggest trying that method instead.
A left join works perfectly for me.
data table1;
input ID State $;
cards;
101 NY
101 TX
103 NY
104 NY
102 TX
;
run;
data table2;
input State $ Day $ Time $20.;
cards;
NY MON 7:00 - 8:00
NY TUES 10:00 - 10:30
NY WED 11:00 - 11:45
TX FRI 8:00 - 9:30
TX SAT 10:00 - 11:00
TX MON 9:00 - 11:00
;
run;
proc sort data=table1; by state;
proc sort data=table2; by state;
proc sql;
create table want as
select a.id, a.state, b.day, b.time
from table1 as a
left join table2 as b
on a.state=b.state
order by a.id;
quit;
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.