topic Re: merging datasets in SAS Programming

merging datasets

mlogan — Tue, 29 Mar 2016 16:02:06 GMT

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.

Re: merging datasets

Reeza — Tue, 29 Mar 2016 15:55:42 GMT

What have you tried?

Is there any further logic beyond joing by State?

Re: merging datasets

mlogan — Tue, 29 Mar 2016 16:02:58 GMT

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,

Re: merging datasets

Reeza — Tue, 29 Mar 2016 16:11:55 GMT

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;

Re: merging datasets

mlogan — Tue, 29 Mar 2016 17:00:42 GMT

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;

Re: merging datasets

Reeza — Tue, 29 Mar 2016 17:07:45 GMT

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

Re: merging datasets

mlogan — Tue, 29 Mar 2016 17:13:21 GMT

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

Re: merging datasets

Reeza — Tue, 29 Mar 2016 17:17:57 GMT

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-data-AKA-generate/ta-p/258712

Re: merging datasets

Reeza — Tue, 29 Mar 2016 17:24:08 GMT

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.

Re: merging datasets

Reeza — Tue, 29 Mar 2016 19:56:20 GMT

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;