Where statement?

[ybz12003]

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;

[Reeza]

Slightly different approach:

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

[MINX]

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;

[ybz12003]

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

[PGStats · Accepted solution]

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;

[ybz12003]

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