BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manya92
Fluorite | Level 6
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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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
Diamond | Level 26

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

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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