DATA Step, Macro, Functions and more

left join usage

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

left join usage

I used the following code.

proc sql;
	create table fault_CNT_only as
	select Unit, fault_code, count(*) as fault_count
	from all_units_results
	group by Unit, fault_code;
quit;


proc sql;
	create table fault_CNT as
	select A.Unit, A.fault_code, count(*) as fault_count,
	B.sw_install_date
	from all_units_results A left join InstallDate B
	on A.Unit=B.vehicle_no
	group by Unit, fault_code
	;
quit;

 

 

The output of fault_CNT_only looks like:

 

8116 20-1803 1
8118 20-0715 1
8119 20-1801 2
8119 20-1802 2
8119 20-1803 2
8119 20-1804 3
8119 20-1806 2
8133 20-0794 1

 

while the output of fault_CNT looks like:

 

8116 20-1803 1 02JAN2015
8118 20-0715 1 01JAN2015
8119 20-1801 2 01JAN2015
8119 20-1801 2 01JAN2015
8119 20-1802 2 01JAN2015
8119 20-1802 2 01JAN2015
8119 20-1803 2 01JAN2015
8119 20-1803 2 01JAN2015
8119 20-1804 3 01JAN2015
8119 20-1804 3 01JAN2015
8119 20-1804 3 01JAN2015
8119 20-1806 2 01JAN2015
8119 20-1806 2 01JAN2015
8133 20-0794 1 01JAN2015

 

How can I get the same number of rows for fault_CNT as in fault_CNT_only?

 

Thanks for your help.

 


Accepted Solutions
Solution
‎08-16-2017 04:47 PM
Frequent Contributor
Posts: 132

Re: left join usage

You have to remove your sw_install_date variable that you are brining in from the InstallDate dataset to get the same count.  If you need the sw_install_date variable, then you should put it in the GROUP BY and before the count() function in your SELECT statement, although it doesn't mean that you will get the same count.  If you have a Unit and Fault_Code combination with multiple sw_install_date values, then you will get more records than the original PROC SQL.

View solution in original post


All Replies
Super User
Posts: 22,875

Re: left join usage

[ Edited ]

It looks like you'll need to aggregate/remove the date then somehow. How do you want the date information to be shown?

 

EDIT: Fixed spelling of word 'date'

 

Solution
‎08-16-2017 04:47 PM
Frequent Contributor
Posts: 132

Re: left join usage

You have to remove your sw_install_date variable that you are brining in from the InstallDate dataset to get the same count.  If you need the sw_install_date variable, then you should put it in the GROUP BY and before the count() function in your SELECT statement, although it doesn't mean that you will get the same count.  If you have a Unit and Fault_Code combination with multiple sw_install_date values, then you will get more records than the original PROC SQL.

Frequent Contributor
Posts: 77

Re: left join usage

There are probably multiple sw_install_date values. They may differ by minutes or seconds. I'm only interested in a resolution of days. Is there some way to resolve this variable to a resolution of days?
Frequent Contributor
Posts: 132

Re: left join usage

I'm not sure I understand what you mean when you say "days". I would recommend giving an example of the output dataset that you are looking to create based on the input data that you currently have.
Frequent Contributor
Posts: 77

Re: left join usage

Dear dcruik,

 

I'm not sure I explained the problem well enough. thank you for your original comment. I used your idea and it worked correctly. The code I used has a minor change in the select ordering from what you suggested. 

 

proc sql;
	create table fault_CNT as
	select A.Unit, A.fault_code, count(*) as fault_count,
		B.sw_install_date
	from all_units_results A left join InstallDate B
	on A.Unit=B.vehicle_no
	group by Unit, fault_code, B.sw_install_date
	;
quit;

The final output has no duplicates and the same number of rows as fault_CNT_only.

 

8116 20-1803 1 02JAN2015
8118 20-0715 1 01JAN2015
8119 20-1801 2 01JAN2015
8119 20-1802 2 01JAN2015
8119 20-1803 2 01JAN2015
8119 20-1804 3 01JAN2015
8119 20-1806 2 01JAN2015
8133 20-0794 1 01JAN2015

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 166 views
  • 0 likes
  • 3 in conversation