topic SAS in SAS Programming

SAS

manya92 — Tue, 24 Jul 2018 14:36:37 GMT

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 ?

Re: SAS

Reeza — Tue, 24 Jul 2018 14:47:35 GMT

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 ?

Re: SAS

PaigeMiller — Tue, 24 Jul 2018 14:54:30 GMT

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