BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dhana18
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dhana18
Obsidian | Level 7

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

8 REPLIES 8
Reeza
Super User

Expected output?

Dhana18
Obsidian | Level 7

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

Dhana18
Obsidian | Level 7
would like to have merged data set without losing data from both table.
Reeza
Super User

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.

 

Dhana18
Obsidian | Level 7

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.

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

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?

Reeza
Super User

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

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
  • 8 replies
  • 1049 views
  • 1 like
  • 2 in conversation