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 ?
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 ?
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 ?
@manya92 could you provide meaningful titles on your posts from now on? SOmething like "Two PROC SQLs not producing same results". Thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.