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

I have two datasets and there are a couple of things that I would like to do. 1.) Dataset1 has the variables: Code, Description, Type. Dataset2 has just the code but it is masked. I would like to first mask the code variable in dataset1 with an "X" as the 4th character replacement in the string. Then I would like to take Datasets 1 & 2 and match the masked with the unmasked to create the dataset below. Also, I wanted to know if there was a way to partially match merge the datasets to get the results below. The only code that I have is the merging. All it is doing is stacking the data not replacing the data as expected. 2.) I wanted to see if I could link the variables code to the description (i.e. 2Y5X1 will always be associated with Description: Apples and Type: A. What I have been doing is the long way...manually changing it in excel, sorting it by ascending, copying it back into dataset 1.  I hope that my question is clear. Please see below.

 

DataSet1     
CodeDescriptionType     
2Y521ApplesA     
2Y522OrangesO     
2Y523BananasB     
2Y524PearsP     
2Y525GrapesG     
2Y526RaspberriesR     
2Y527KiwiK     
2Y528CantaloupeC     
        
DataSet2   
Mask Dataset to Look like this with the X as replacement for the 4th character in a string   
        
Code       
2Y5X1       
2Y5X2       
2Y5X3       
2Y5X4       
2Y5X5       
2Y5X6       
2Y5X7       
2Y5X8       
        
Then Merge with DataSet1 to Match DataSet2    
DataSet2DescriptionType     
2Y5X1ApplesA<---Then always associate 2Y5X1 with Apples and A
2Y5X2OrangesO     
2Y5X3BananasB     
2Y5X4PearsP     
2Y5X5GrapesG     
2Y5X6RaspberriesR     
2Y5X7KiwiK     
2Y5X8CantaloupeC     
 /*This is after I mask it in excel and import*/ 

proc sort data=dataset1; 
by code; 
run;
 
proc sort data=dataset2; 
by code; 
run;

Data Merge; 
merge dataset1 dataset2; 
by code; 
run;  

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16
data DataSet1;
input Code$ Description$ Type$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 Apples A   
2Y522 Oranges O   
2Y523 Bananas B	
2Y524 Pears P 	
2Y525 Grapes G 	 	 	 
;

data DataSet2;
input Code$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 
2Y522 
2Y523 
2Y524 
2Y525 
;

proc sort data=dataset1;
by code;
run;

proc sort data=dataset2;
by code;
run;

data want;
merge dataset1 dataset2;
by code;
run;
Thanks,
Jag

View solution in original post

1 REPLY 1
Jagadishkatam
Amethyst | Level 16
data DataSet1;
input Code$ Description$ Type$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 Apples A   
2Y522 Oranges O   
2Y523 Bananas B	
2Y524 Pears P 	
2Y525 Grapes G 	 	 	 
;

data DataSet2;
input Code$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 
2Y522 
2Y523 
2Y524 
2Y525 
;

proc sort data=dataset1;
by code;
run;

proc sort data=dataset2;
by code;
run;

data want;
merge dataset1 dataset2;
by code;
run;
Thanks,
Jag

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

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
  • 1 reply
  • 1326 views
  • 0 likes
  • 2 in conversation