Help using Base SAS procedures

Where statement?

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

Where statement?

[ Edited ]

Hello:

 

I  have the  program below.   I would like to create a new column "match" where groupproduct in the macro list.  Please advice how.  Thanks.

 

 

data test1;
	infile datalines dlm='|';
	input Author : $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 sql;
	select strip(analysis)||'#'||strip(Product) into : AnalysisGrouplist separated by '" "'
		from test1
			where analysis ne ' ' and product ne ' ';
quit;

%put &AnalysisGrouplist;

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|
;

data test2;
	set test2;
	GroupProduct=strip(analysis)||'#'||strip(Product);
run;

data test2match;
	set test2;
	where groupproduct in ("&AnalysisGrouplist");
run;

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     


Accepted Solutions
Solution
‎02-01-2018 09:36 AM
Esteemed Advisor
Posts: 5,541

Re: Where statement?

If you want to match on two columns and the analysis-product combimations in test1 are unique, you can simply use a SQL join:

 

proc sql;
create table test2Match as
select a.*
from test2 as a inner join
test1 as b on a.Analysis=b.Analysis and a.Product=b.Product;
quit;
PG

View solution in original post


All Replies
Super User
Posts: 23,776

Re: Where statement?

Slightly different approach:

 

proc sql;
create table want as
select *
from have
where groupProduct in (select ...  as GroupProduct from listTable); 
quit;
Contributor
Posts: 29

Re: Where statement?

A little correction:

1. Add quotation marks into macro variable

      proc sql;

        select "'"||strip(analysis)||'#'||strip(Product)||"'" into : AnalysisGrouplist separated by ','

        from test1

        where analysis ne ' ' and product ne ' ';

      quit;

 

2. Remove quotation marks to call  macro variable

      data test2match;

        set test2;

        where groupproduct in (&AnalysisGrouplist.);

      run;

 

Super Contributor
Posts: 398

Re: Where statement?

Thanks, MINX.   Your modified codes does solve all of the WANTing notes from the Log.  

Solution
‎02-01-2018 09:36 AM
Esteemed Advisor
Posts: 5,541

Re: Where statement?

If you want to match on two columns and the analysis-product combimations in test1 are unique, you can simply use a SQL join:

 

proc sql;
create table test2Match as
select a.*
from test2 as a inner join
test1 as b on a.Analysis=b.Analysis and a.Product=b.Product;
quit;
PG
Super Contributor
Posts: 398

Re: Where statement?

Thanks, PGStats.  The Inner Join codes are awesome, it reduces a lots of run time.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 229 views
  • 2 likes
  • 4 in conversation