topic Where statement? in SAS Procedures

Where statement?

ybz12003 — Wed, 31 Jan 2018 16:56:21 GMT

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;

Re: Where statement?

Reeza — Wed, 31 Jan 2018 16:53:38 GMT

Slightly different approach:

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

Re: Where statement?

MINX — Wed, 31 Jan 2018 19:06:54 GMT

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;

Re: Where statement?

PGStats — Wed, 31 Jan 2018 19:12:37 GMT

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;

Re: Where statement?

ybz12003 — Thu, 01 Feb 2018 14:33:48 GMT

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

Re: Where statement?

ybz12003 — Thu, 01 Feb 2018 14:36:05 GMT

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