DATA Step, Macro, Functions and more

Merging

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Merging

Hello I am trying to merge two data sets with a variable that is repeated in both data sets. For example;

data cm;

infile cards;

input pid 1-3 @5 date date9. medication $15-35;

format date date9.;

cards;

101 16Nov2009 GCNAAT

101 17Nov2009 GCCULTURE

102 16Nov2009 GCNAAT

102 17Nov2009 GCNAAT

102 18Nov2009 GCCULTURE

103 18Nov2009GCCULTURE

104 19Nov2009 GCCULTURE

105 17Nov2009 GCNAAT

run;

 

 

data ae;

input pid 1-3 @5 date date9. event $ 15-35;

format date mmddyy10.;

cards;

101 16Nov2009 AZE

102 16Nov2009 GEN

102 17Nov2009 FLU

102 18Nov2009MEN

103 17Nov2009 GEN

103 18Nov2009 AZI

105 17Nov2009 MEN

run;

Need your help please.

 


Accepted Solutions
Solution
4 weeks ago
Occasional Contributor
Posts: 14

Re: Merging

Thank you for your quick response. This was just an example.  I have many variables like age , sex , zip etc  in the data sets I am trying to merge.  So do I need to list all of the variables in select  like you have below?

View solution in original post


All Replies
Super User
Posts: 23,724

Re: Merging

Expected output?

Occasional Contributor
Posts: 14

Re: Merging

would like to have table without losing data from both table.

Occasional Contributor
Posts: 14

Re: Merging

would like to have merged data set without losing data from both table.
Super User
Posts: 23,724

Re: Merging

There still needs to be more clarification.

Record 102 has 3 records in table 1 and 3 records in table 2.

How many records do you expect as output? What exactly are you expecting as output? 


@Dhana18 wrote:
would like to have merged data set without losing data from both table.

 

Occasional Contributor
Posts: 14

Re: Merging

Thank you. I am trying to merge them by id and date, so if any of these (id or date) are present in any of these datasets then I want them in the merged data set.

Super User
Posts: 23,724

Re: Merging

proc sql;
create table want as
select t1.pid as cm_PID, t1.date as cm_DATE, t1.medication, 
       t2.pid as ae_PID, t2.date as ae_date, t2.event
from cm as t1
full join ae as t2
on t1.pid=t2.pid and t1.date=t2.date;
quit;
Solution
4 weeks ago
Occasional Contributor
Posts: 14

Re: Merging

Thank you for your quick response. This was just an example.  I have many variables like age , sex , zip etc  in the data sets I am trying to merge.  So do I need to list all of the variables in select  like you have below?

Super User
Posts: 23,724

Re: Merging


@Dhana18 wrote:

Thank you for your quick response. This was just an example.  I have many variables like age , sex , zip etc  in the data sets I am trying to merge.  So do I need to list all of the variables in select  like you have below?


Depends. You can use wild card references but then you'll have issues with any duplicates.

 

select t1.*, t2.*

This selects all columns but if you have columns with the same name it's problematic.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 103 views
  • 1 like
  • 2 in conversation