- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Slightly different approach:
proc sql;
create table want as
select *
from have
where groupProduct in (select ... as GroupProduct from listTable);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, MINX. Your modified codes does solve all of the WANTing notes from the Log.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, PGStats. The Inner Join codes are awesome, it reduces a lots of run time.