DATA Step, Macro, Functions and more

merging datasets

Reply
Regular Contributor
Posts: 215

merging datasets

[ Edited ]

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.

Super User
Posts: 17,819

Re: merging datasets

What have you tried?

 

Is there any further logic beyond joing by State?

Regular Contributor
Posts: 215

Re: merging datasets

Hi Reeza,
I want all three observation of each by group (since they are unique observation) in Table2 merge with each observation of Table1.
I listed in the post what I tried so far. Thanks,
Super User
Posts: 17,819

Re: merging datasets

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;
Regular Contributor
Posts: 215

Re: merging datasets

[ Edited ]

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;

Super User
Posts: 17,819

Re: merging datasets

So what's the expected output for that input data? Please try and specify your problem in detail at the beginning...

Regular Contributor
Posts: 215

Re: merging datasets

Here it is in detail:

Table 1:

ID State
101 NY
101 TX
103 NY
104 NY
102 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
103 NY MON 7:00 - 8:00
103 NY TUES 10:00 - 10:30
103 NY WED 11: - 11:45
104 NY MON 7:00 - 8:00
104 NY TUES 10:00 - 10:30
104 NY WED 11: - 11:45
102 TX FRI 8:00 - 9:30
102 TX SAT 10:00 - 11:00
102 TX MON 9:00 - 11:00
Super User
Posts: 17,819

Re: merging datasets

Please post your data as a data step, its a pain to type it out each time.

 

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Super User
Posts: 17,819

Re: merging datasets

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.

 

 

 

Super User
Posts: 17,819

Re: merging datasets

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;
Ask a Question
Discussion stats
  • 9 replies
  • 298 views
  • 3 likes
  • 2 in conversation