Help using Base SAS procedures

join two dataset

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

join two dataset

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 


Accepted Solutions
Highlighted
Solution
‎03-08-2018 11:59 AM
Super User
Posts: 13,523

Re: join two dataset

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


All Replies
Super Contributor
Posts: 284

Re: join two dataset

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; 

Super User
Posts: 13,523

Re: join two dataset

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.

Contributor
Posts: 67

Re: join two dataset

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
Contributor
Posts: 67

Re: join two dataset

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

Highlighted
Solution
‎03-08-2018 11:59 AM
Super User
Posts: 13,523

Re: join two dataset

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 203 views
  • 1 like
  • 3 in conversation