Help using Base SAS procedures

Proc SQL join question?

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

Proc SQL join question?

Hello:

 

I have two dataset, test1 and test2.   I would like to find the match based on the following situation:

 

(Test1 "Analysis" = Test2 "Analysis") and  [ Test1 "Product" = any of the Test2 ("Class" / "Component" / "Product") ]

 

I have the inner join code below, I found out that the "Cleft Lip +/- Palate" and "ATENOLOL" are shown twice in the result.   Any idea why?  How to fix the problem?  Thanks.

 

data test1;

infile datalines dlm='|';

input name : $20. Analysis : $100. Product : $100.;

datalines;

 

Ali| Cleft Lip +/- Palate| LABETOLOL|

Ali| Cleft Lip +/- Palate| ATENOLOL|

Ali| VSDs (Excludes VSDavc)| ATENOLOL|

Ali| VSDs (Excludes VSDavc)| FLUCONAZOLE|

Carol| Choanal Atresia| LEVOTHYROXINE SODIUM|

Carol| Cleft Lip +/- Palate| ATENOLOL|

Carol| Intestinal Atresia/Stenosis| EPHEDRA|

Malyn| TGA +/- DORV| ALBUTEROL SULFATE|

Malyn| TOF +/- PA, DORV -TGA| BUTALBITAL|

Malyn| Esoph Atresia +/- TEF| FLONASE|

;

 

proc sort data=test1; by analysis; run;

 

data test2;

infile datalines dlm='|';

input Analysis : $100. Class : $100. Component : $100. Product : $100.;

datalines;

 

Cleft Lip +/- Palate| LEVOTHYROXINE SODIUM| | |

VSDs (Excludes VSDavc)| ANTI/VITA| | |

Choanal Atresia| LEVOTHYROXINE SODIUM| | |

TGA +/- DORV| FLONASE| | |

Cleft Lip +/- Palate| | ATENOLOL| |

Intestinal Atresia/Stenosis| | EPHEDRA| |

VSDs (Excludes VSDavc)| | BUTALBITAL| |

Esoph Atresia +/- TEF| | FLONASE| |

Cleft Lip +/- Palate| | | LABETOLOL|

Esoph Atresia +/- TEF| | | ATENOLOL|

VSDs (Excludes VSDavc)| | | ALBUTEROL SULFATE|

TOF +/- PA, DORV -TGA| | | BUTALBITAL|

Choanal Atresia| | | LEVOTHYROXINE SODIUM|

;

 

proc sort data=test2; by analysis component; run;

 

proc sql;

create table test2Matchtotal as

select a.*

from test2 as a inner join

test1 as b on a.Analysis=b.Analysis and (a.class=b.product or a.component=b.product or a.product=b.product);

quit;

 

 


Accepted Solutions
Solution
‎02-02-2018 12:51 PM
Super User
Posts: 13,583

Re: Proc SQL join question?

The values are duplicated because this:

(a.class=b.product or a.component=b.product or a.product=b.product);

creates a match for potentially 3 conditions but the requested output (a.*) doesn't change the result selected

 

If you don't want duplicated results one approach is the distinct predicate.

 

Select distinct a.*

View solution in original post


All Replies
Super User
Posts: 23,776

Re: Proc SQL join question?

Post your code into the code editor. 

And format and comment it. 

 

Solution
‎02-02-2018 12:51 PM
Super User
Posts: 13,583

Re: Proc SQL join question?

The values are duplicated because this:

(a.class=b.product or a.component=b.product or a.product=b.product);

creates a match for potentially 3 conditions but the requested output (a.*) doesn't change the result selected

 

If you don't want duplicated results one approach is the distinct predicate.

 

Select distinct a.*

Super Contributor
Posts: 398

Re: Proc SQL join question?


Cool !!!

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 134 views
  • 1 like
  • 3 in conversation