topic join two dataset in SAS Procedures

join two dataset

Barkamih — Wed, 07 Mar 2018 18:12:27 GMT

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

Re: join two dataset

saspert — Wed, 07 Mar 2018 19:11:37 GMT

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;

Re: join two dataset

ballardw — Wed, 07 Mar 2018 20:41:49 GMT

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.

Re: join two dataset

Barkamih — Thu, 08 Mar 2018 12:50:29 GMT

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

Re: join two dataset

Barkamih — Thu, 08 Mar 2018 13:02:23 GMT

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

Re: join two dataset

ballardw — Thu, 08 Mar 2018 16:47:21 GMT

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.