SAS

[manya92]

proc sql ;
	create table _01_iv_of_i as
	select distinct a.*, b.code as b_code, b.index_of, c.code as c_code, index_proc
	from derived._01_ser_inf as a 
	inner join derived._01_of as b
	on a.enrolid = b.enrolid 
	inner join derived._01_iv as c
	on a.enrolid = c.enrolid ;
quit ;/*15257338*/

%count_pt ;
%print ;
/*UNION TO GET ALL CODES IN ONE ROW */
proc sql ;	
	create table derived._01_ic_of_i2 as
	select distinct enrolid, index_i as svcdate format mmddyy10., code 
	from _01_iv_of_i 
	union 
	select distinct enrolid , index_of as svcdate format mmddyy10. , b_code as code
	from _01_iv_of_i
	union 
	select distinct enrolid, index_proc as svcdate format mmddyy10., c_code as code
	from _01_iv_of_i;
quit;/*184553*/

why do i have less observations in the second step. Should i not be having double  or more ?

[Reeza · Accepted solution]

Union Vs UNION ALL. 

Union does not inlcude duplicates, union all includes duplicates. 
However, since in this case it's not true duplicates you definitely want UNION ALL.

---

@manya92 wrote:

proc sql ;
	create table _01_iv_of_i as
	select distinct a.*, b.code as b_code, b.index_of, c.code as c_code, index_proc
	from derived._01_ser_inf as a 
	inner join derived._01_of as b
	on a.enrolid = b.enrolid 
	inner join derived._01_iv as c
	on a.enrolid = c.enrolid ;
quit ;/*15257338*/

%count_pt ;
%print ;
/*UNION TO GET ALL CODES IN ONE ROW */
proc sql ;	
	create table derived._01_ic_of_i2 as
	select distinct enrolid, index_i as svcdate format mmddyy10., code 
	from _01_iv_of_i 
	union 
	select distinct enrolid , index_of as svcdate format mmddyy10. , b_code as code
	from _01_iv_of_i
	union 
	select distinct enrolid, index_proc as svcdate format mmddyy10., c_code as code
	from _01_iv_of_i;
quit;/*184553*/

why do i have less observations in the second step. Should i not be having double  or more ?

---

[PaigeMiller]

@manya92 could you provide meaningful titles on your posts from now on? SOmething like "Two PROC SQLs not producing same results". Thanks.