to merge two datasets

santosh_pat69 — Thu, 23 Jun 2016 20:51:00 GMT

Hi All,

Please help achieve the below result
I have two tables, Dept1 and details, now i need to create a new table where i need to add the date joining based on the Dept1 column values and the Leave date would be set to a default date of 31Dec9999 for all the columns Emp_Id is missing.

Table 1

Details
Emp_Id	Name	Dept	Salary	Date of Joining	LOP	Dept1
A001	A	Tech	12300	13-Jan-16	14-Jan-16	Tech
A002	B	Admin	4500	16-Apr-15	17-Apr-15	Admin
A003	C	Hosp	8970	16-Apr-15	17-Apr-15	Hosp
A004	D	Tech	8765	2-Apr-15	2-Apr-15	Tech
A005	E	Tech	43567	9-Apr-15	9-Apr-15	Tech
A006	F	Hosp	9087	28-Apr-15	12-May-15	Hosp
A087	G	Admin	9876	20-Jan-13	21-Jun-16	Admin
E00057	H	Tech	12345	17-Mar-15	19-Mar-15	Tech
E0041	I	Admin	7645	17-Mar-15	18-Mar-15	Admin
E00062	J	Tech	89075	14-Aug-14	14-Aug-14	Tech
E00015	K	Tech	34565	22-May-13	9-Oct-13	Tech
E00076	L	Hosp	567	20-Jan-13	22-Jun-16	Hosp
	M M	Tech	8970			Tech
	NN	Admin	8765			Admin
	OO	Hosp	43567			Hosp
	PP	Tech	9087			Tech
	QQ	Tech	9876			Tech
	RR	Hosp	7645			Hosp
	SS	Admin	89075			Admin
	TT	Tech	34565			Tech
	UU	Admin	567			Admin
	VV	Tech	7645			Tech
	WW	Tech	89075			Tech
	XX	Hosp	34565			Hosp
	YY	Hosp	89075			Hosp
	ZZ	Hosp	34565			Hosp

Table 2:

Dept 1
Dept	Date of Joining	Date of Leave
Tech	13-Jan-16	14-Jan-16
Admin	16-Apr-15	17-Apr-15
Hosp	16-Apr-15	17-Apr-15
Tech	2-Apr-15	2-Apr-15
Tech	9-Apr-15	9-Apr-15
Hosp	28-Apr-15	12-May-15
Admin	20-Jan-13	21-Jun-16
Tech	17-Mar-15	19-Mar-15
Admin	17-Mar-15	18-Mar-15
Tech	14-Aug-14	14-Aug-14
Tech	22-May-13	9-Oct-13
Hosp	20-Jan-13	22-Jun-16

Results:

			Result
Emp_Id	Name	Dept	Salary	Date of Joining	LOP	Dept1
A001	A	Tech	12300	13-Jan-16	14-Jan-16	Tech
A002	B	Admin	4500	16-Apr-15	17-Apr-15	Admin
A003	C	Hosp	8970	16-Apr-15	17-Apr-15	Hosp
A004	D	Tech	8765	2-Apr-15	2-Apr-15	Tech
A005	E	Tech	43567	9-Apr-15	9-Apr-15	Tech
A006	F	Hosp	9087	28-Apr-15	12-May-15	Hosp
A087	G	Admin	9876	20-Jan-13	21-Jun-16	Admin
E00057	H	Tech	12345	17-Mar-15	19-Mar-15	Tech
E0041	I	Admin	7645	17-Mar-15	18-Mar-15	Admin
E00062	J	Tech	89075	14-Aug-14	14-Aug-14	Tech
E00015	K	Tech	34565	22-May-13	9-Oct-13	Tech
E00076	L	Hosp	567	20-Jan-13	22-Jun-16	Hosp
	M M	Tech	8970	13-Jan-16	12-Dec-99	Tech
	NN	Admin	8765	16-Apr-15	12-Dec-99	Admin
	OO	Hosp	43567	16-Apr-15	12-Dec-99	Hosp
	PP	Tech	9087	2-Apr-15	12-Dec-99	Tech
	QQ	Tech	9876	9-Apr-15	12-Dec-99	Tech
	RR	Hosp	7645	28-Apr-15	12-Dec-99	Hosp
	SS	Admin	89075	20-Jan-13	12-Dec-99	Admin
	TT	Tech	34565	17-Mar-15	12-Dec-99	Tech
	UU	Admin	567	20-Jan-13	12-Dec-99	Admin
	VV	Tech	7645	14-Aug-14	12-Dec-99	Tech
	YY	Hosp	89075	20-Jan-13	12-Dec-99	Hosp
	ZZ	Hosp	34565	20-Jan-13	12-Dec-99	Hosp

Re: to merge two datasets

RahulG — Fri, 24 Jun 2016 03:52:13 GMT

It seems you do not have primary key column in two tables. Whatever way you do the output would need to verified throughly.

If you are very sure that records that need to be joined are in same order in both the tables then in that case create new numeric column NEW_COLUMN in both the table and store record number that may be _N_ or something else whatever you like. This new column would act as suggorate key and now you can join two table with NEW_COLUMN and dept1.

Re: to merge two datasets

Kurt_Bremser — Fri, 24 Jun 2016 06:41:48 GMT

You do not have a key that sufficently indicates records to be joined, therefore this is not possible.

topic Re: to merge two datasets in SAS Programming

to merge two datasets

Re: to merge two datasets

Re: to merge two datasets