SAS Procedures

Help using Base SAS procedures
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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