Union All

[Ranjeeta]

proc sql; create table deno_2 as

select HCNE, CIHI_key, inst_no, Fac_no, Facility_LHIN, patient_LHIN, POSTAL_CODE,Fiscal_QTR,a.ADM_DT,a.ADM_TM

from DAD_D_4 as a

union all

select HCNE, CIHI_key, inst_no, Facility_no, Facility_LHIN, patient_LHIN, POSTAL_CODE, Fiscal_QTR,b.REG_DT,b.REG_TM

from NAC_D_8 as b

;

quit;

How would I combine the two datasets by including the last two variables from the 2 datasets which are different 

[novinosrin · Accepted solution]

Try OUTER UNION CORR

proc sql; create table deno_2 as

select HCNE, CIHI_key, inst_no, Fac_no, Facility_LHIN, patient_LHIN, POSTAL_CODE,Fiscal_QTR,a.ADM_DT,a.ADM_TM

from DAD_D_4 as a

outer union corr

select HCNE, CIHI_key, inst_no, Facility_no, Facility_LHIN, patient_LHIN, POSTAL_CODE, Fiscal_QTR,b.REG_DT,b.REG_TM

from NAC_D_8 as b

;

quit;

[Tom]

Why are you using SQL Union?  Why not just SET the datasets together?

data deno_2;
 set DAD_D_4 NAC_D_8 ;
run;

[PGStats]

Looks like you should be doing a JOIN instead of a UNION.

proc sql; 
create table deno_2 as
select 
	a.HCNE, 
	a.CIHI_key, 
	a.inst_no, 
	a.Fac_no, 
	a.Facility_LHIN, 
	a.patient_LHIN, 
	a.POSTAL_CODE,
	a.Fiscal_QTR,
	a.ADM_DT,
	a.ADM_TM,
	b.reg_dt,
	b.reg_tm
from 
	DAD_D_4 as a left join 
	NAC_D_8 as b on
		a.HCNE = b.HCNE and
		a.CIHI_key = b.CIHI_key and
		a.inst_no = b.inst_no and
		a.Fac_no = b.facility_no and
		a.Facility_LHIN = b.facility_LHIN and
		a.patient_LHIN = patient_LHIN and 
		a.POSTAL_CODE = b.POSTAL_CODE and
		a.Fiscal_QTR = b.Fiscal_QTR;
quit;

Just a guess.

[novinosrin]

Sir @PGStats  Very well read, I couldn't gauge that at all.

Hmm no wonder

(Pierre=Other peers) will always be zero. 

[Ranjeeta]

Thankyou 

I did intent to concatenate all the results similar to the set operator in data step