Solved
Contributor
Posts: 68

# SAS

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 ?

Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 23,937

## Re: SAS

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 ?

All Replies
Solution
3 weeks ago
Super User
Posts: 23,937

## Re: SAS

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 ?

Posts: 3,249

## Re: SAS

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

--
Paige Miller
☑ This topic is solved.