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

Hi guys

 

I'm looking for code to join four datasets matching columns, the name of my datasets are p1, p2, p2, p3, and p4.

I would like to join them by COW_ID and PR variables.

 

thanks guys 

 

Regards

 

Ibrahim 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Combining data sets vertically (appending) can be easily done with

 

data want;

   set data1 data2 data3 data4; /*or what ever the data set names are*/

run;

 

If you want the result in a specific order sort after combining.

 

If you have no duplicates of the variables you mentioned in any of the datasets, if each data set is sorted by the variables, then a MERGE would interleave the data but duplicate values of the key variables would result in other variables being replaced by values from other sets depending on the order of the data.

View solution in original post

5 REPLIES 5
saspert
Pyrite | Level 9

proc sort data=p1;

by COW_ID PR;

run;

proc sort data=p2;

by COW_ID PR;

run;

proc sort data=p3;

by COW_ID PR;

run;

proc sort data=p4;

by COW_ID PR;

run;

data mrgall;

merge p1(in=a) p2(in=b) p3(in=c) p4 (in=d);

by COW_ID PR;

if a and b and c and d then output;

run; 

ballardw
Super User

Do any of these data sets have duplicate values of combinations of COW_ID and PR?

This is important as how those are supposed to match up may be an issue when considering one-to-many or many-to-many matching.

 

Best might be to provide a small sample from each of your sets and what the expected result for those samples would look like.

Barkamih
Pyrite | Level 9
this is a sample of two datasets 
HERD	          COW_ID       CALVING_DATE   CAease    Pr      TEST_DATE      MILK_KG	FAT_KG
60007197	666355946	23/11/2011	1	1	29/4/2014	34.6	1.0553
60007197	666355946	23/11/2011	1	1	12/6/2014	19.8	0.6237
60007197	666355946	23/11/2011	1	1	19/8/2014	30.4	0.988
60007197	666355946	23/11/2011	1	1	27/11/2014	11.4	0.3751
59996534	816229712	16/4/2013	2	1	25/2/2014	22.6	1.5097
59996534	816229712	16/4/2013	2	1	27/3/2014	23.8	1.6065
59996534	816229712	16/4/2013	2	1	22/5/2014	21.4	1.5536
59996534	816229712	16/4/2013	2	1	23/9/2014	14.6	0.9884
60005742	817069792	20/4/2013	3	1	5/3/2014	33.4	1.0588



all my data looks like this. So, in thsi case i should have a code to put them in vertical psotions.
regards
Barkamih
Pyrite | Level 9

my dataset has the same variables, I just want to put them together. 

ballardw
Super User

Combining data sets vertically (appending) can be easily done with

 

data want;

   set data1 data2 data3 data4; /*or what ever the data set names are*/

run;

 

If you want the result in a specific order sort after combining.

 

If you have no duplicates of the variables you mentioned in any of the datasets, if each data set is sorted by the variables, then a MERGE would interleave the data but duplicate values of the key variables would result in other variables being replaced by values from other sets depending on the order of the data.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 840 views
  • 1 like
  • 3 in conversation