DATA Step, Macro, Functions and more

SAS

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

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 ?


 

View solution in original post


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 ?


 

Respected Advisor
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 54 views
  • 1 like
  • 3 in conversation