topic Re: Union All in SAS Programming

Union All

Ranjeeta — Tue, 24 Sep 2019 16:27:31 GMT

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

Re: Union All

novinosrin — Tue, 24 Sep 2019 16:32:32 GMT

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;

Re: Union All

Tom — Tue, 24 Sep 2019 16:46:51 GMT

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

data deno_2;
 set DAD_D_4 NAC_D_8 ;
run;

Re: Union All

PGStats — Tue, 24 Sep 2019 22:18:34 GMT

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.

Re: Union All

novinosrin — Wed, 25 Sep 2019 13:19:37 GMT

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

Hmm no wonder

(Pierre=Other peers) will always be zero.

Re: Union All

Ranjeeta — Fri, 27 Sep 2019 16:42:55 GMT

Thankyou

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