BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
Reeza
Super User

Slightly different approach:

 

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

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
Rhodochrosite | Level 12

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

PGStats
Opal | Level 21

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
ybz12003
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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