BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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.

9 REPLIES 9
Reeza
Super User

What have you tried?

 

Is there any further logic beyond joing by State?

mlogan
Lapis Lazuli | Level 10
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,
Reeza
Super User

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;
mlogan
Lapis Lazuli | Level 10

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;

Reeza
Super User

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

mlogan
Lapis Lazuli | Level 10
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
Reeza
Super User

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...

Reeza
Super User

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.

 

 

 

Reeza
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1015 views
  • 3 likes
  • 2 in conversation